melo 唐

1.Select 搭配 SP

C#:

SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["TEST"]);
myConnection.Open();

SqlCommand myCommand = new SqlCommand();
myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;//宣告為SP
myCommand.CommandText = "SelectExample"; //SP名稱

SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);

DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);
myConnection.Close();

foreach (DataRow myRow in myDataSet.Tables[0].Rows)
{
Response.Write(myRow["FirstName"] + "-" + myRow["LastName"]+"</br>");//印出資料庫中欄位為FirstName以及LastName
}

TSQL:

Create procedure [dbo].[SelectExample]
as
begin
select top 5 * from Customers
end

2.Insert 搭配 SP

C#:

SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["TEST"]);
SqlCommand myCommand = new SqlCommand();
myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "InsertExample";

myCommand.Parameters.AddWithValue("@FirstName", "Brian");
myCommand.Parameters.AddWithValue("@LastName", "Faley");
myCommand.Parameters.AddWithValue("@City", "Denver");
myCommand.Parameters.AddWithValue("@State", "CO");

try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
myConnection.Close();
}

TSQL:

Create procedure [dbo].[InsertExample]
@FirstName NVarChar(50),
@LastName NVarChar(50),
@City NVarChar(50),
@State NVarChar(50)
as
begin
INSERT INTO Customers (FirstName, LastName, City, [State]) VALUES (@FirstName, @LastName, @City, @State)
end

3.Update 搭配 SP

C#:

try
{
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["TEST"]);
SqlCommand myCommand = new SqlCommand();
myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "UpdateExample";

myCommand.Parameters.AddWithValue("@CustomerID", 1);
myCommand.Parameters.AddWithValue("@FirstName", "Melo");
myCommand.Parameters.AddWithValue("@LastName", "Melo");
myCommand.Parameters.AddWithValue("@City", "Taipei");
myCommand.Parameters.AddWithValue("@State", "Taipei");

myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception ex)
{
throw ex;
}

TSQL:

create procedure [dbo].[UpdateExample]
@CustomerID Int,
@FirstName NVarChar(50),
@LastName NVarChar(50),
@City NVarChar(50),
@State NVarChar(50)
as
begin
UPDATE Customers
SET
FirstName = @FirstName,
LastName = @LastName,
City = @City,
State = @State
WHERE CustomerID = @CustomerID
end

4.Delete 搭配 SP

C#:

try
{
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["TEST"]);
SqlCommand myCommand = new SqlCommand();
myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "DeleteExample";

myCommand.Parameters.AddWithValue("@LastName", "Melo");
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception ex)
{
throw ex;
}

TSQL:

create procedure [dbo].[DeleteExample]
@LastName nvarchar(50)
as
begin
DELETE FROM
Customers
WHERE
[LastName] = @LastName
end