.NET数据库操作集锦

The Stored Procedures
Before you can start coding the data access logic, you need to make sure you have the set of stored
procedures you need in order to retrieve, insert, and update information. The following code shows
the five stored procedures that are needed:

CREATE PROCEDURE InsertEmployee
@EmployeeID int OUTPUT
@FirstName varchar(10),
@LastName varchar(20),
@TitleOfCourtesy varchar(25),
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());
SET @EmployeeID = @@IDENTITY
GO

CREATE PROCEDURE DeleteEmployee
@EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
GO

CREATE PROCEDURE UpdateEmployee
@EmployeeID int,
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10)
AS
UPDATE Employees
SET TitleOfCourtesy = @TitleOfCourtesy,
LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID
GO

CREATE PROCEDURE GetAllEmployees
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
GO

CREATE PROCEDURE CountEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO

CREATE PROCEDURE GetEmployee
@EmployeeID int
AS
SELECT FirstName, LastName, TitleOfCourtesy FROM Employees
WHERE EmployeeID = @EmployeeID
GO

The Data Utility Class
Finally, you need the utility class that performs the actual database operations. This class uses the
stored procedures that were shown in the previous section.
In this example, the data utility class is named EmployeeDB. It encapsulates all the data access
code and database-specific details. Here’s the basic outline:
public class EmployeeDB
{
private string connectionString;
public EmployeeDB()
{
// Get default connection string.
connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
}
public EmployeeDB(string connectionStringName)
{
// Get the specified connection string.
connectionString = WebConfigurationManager.ConnectionStrings["connectionStringName"].ConnectionString;
}
public int InsertEmployee(EmployeeDetails emp)
{ ... }
public void DeleteEmployee(int employeeID)
{ ... }
public void UpdateEmployee(EmployeeDetails emp)
{ ... }
public EmployeeDetails GetEmployee()
{ ... }
public EmployeeDetails[] GetEmployees()
{ ... }
public int CountEmployees()
{ ... }
}

public int InsertEmployee(EmployeeDetails emp)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("InsertEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = emp.FirstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = emp.LastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy",
SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
try
{
con.Open();
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["@EmployeeID"].Value;
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}

public EmployeeDetails GetEmployee(int employeeID)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// Get the first row.
reader.Read();
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
reader.Close();
return emp;
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}

public List<EmployeeDetails> GetEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
// Create a collection for all the employee records.
List<EmployeeDetails> employees = new List<EmployeeDetails>();
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
employees.Add(emp);
}
reader.Close();
return employees;
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}

public void UpdateEmployee(int EmployeeID, string firstName, string lastName,
string titleOfCourtesy)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = firstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = lastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar,
25));
cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = EmployeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}

public void DeleteEmployee(int employeeID)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public int CountEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("CountEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
return (int)cmd.ExecuteScalar();
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}

Using the DataSet in a Custom Data Class
There’s no reason you can’t use the DataSet or DataTable as the return value from a method in your
custom data access class. For example, you could rewrite the GetAllEmployees() method shown
earlier with the following DataSet code:

public DataTable GetAllEmployees(){SqlConnection con = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand("GetEmployee", con);cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));cmd.Parameters["@EmployeeID"].Value = employeeID;SqlDataAdapter da = new SqlDataAdapter(sql, con);DataSet ds = new DataSet();// Fill the DataSet.try{da.Fill(ds, "Employees");return ds.Tables["Employees"];}catch{throw new ApplicationException("Data error.");}}