ADO.NET操作SQL Server：数据库操作类(未封装)

/// <summary>
/// 添加数据
/// </summary>
/// <param name="newEntity"></param>
/// <returns></returns>
public static int Insert(Student newEntity)
{
string sql = @"insert into [Student] ([StudentId],[StudentNo],[IdCard],[RealName]) values (@StudentId,@StudentNo,@IdCard,@RealName)";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
#region MyRegion
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = newEntity.StudentId;
cmd.Parameters.Add("@StudentNo", SqlDbType.Int).Value = newEntity.StudentNo;
cmd.Parameters.Add("@IdCard", SqlDbType.NVarChar).Value = newEntity.IdCard ?? (object)DBNull.Value;
cmd.Parameters.Add("@RealName", SqlDbType.NVarChar).Value = newEntity.RealName ?? (object)DBNull.Value;
#endregion conn.Open();
return cmd.ExecuteNonQuery();
}
}
}

/// <summary>
/// 修改数据
/// </summary>
/// <param name="updateEntity"></param>
/// <returns></returns>
public static int Update(Student updateEntity)
{
string sql = @"update [Student] set [StudentNo]=@StudentNo,[IdCard]=@IdCard,[RealName]=@RealName where [StudentId]=@StudentId";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
#region MyRegion
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = updateEntity.StudentId;
cmd.Parameters.Add("@StudentNo", SqlDbType.Int).Value = updateEntity.StudentNo;
cmd.Parameters.Add("@IdCard", SqlDbType.NVarChar).Value = updateEntity.IdCard ?? (object)DBNull.Value;
cmd.Parameters.Add("@RealName", SqlDbType.NVarChar).Value = updateEntity.RealName ?? (object)DBNull.Value;
#endregion conn.Open();
return cmd.ExecuteNonQuery();
}
}
}

/// <summary>
/// 删除数据
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
public static int Delete(Guid studentId)
{
string sql = @"delete from [Student] where [StudentId]=@StudentId";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = studentId;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}

/// <summary>
/// 读取数据
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static Student Reader(SqlDataReader reader)
{
Student newEntity = new Student();
if (reader != null && !reader.IsClosed)
{
if (reader["StudentId"] != DBNull.Value) newEntity.StudentId = (Guid)reader["StudentId"];
if (reader["StudentNo"] != DBNull.Value) newEntity.StudentNo = (int)reader["StudentNo"];
if (reader["IdCard"] != DBNull.Value) newEntity.IdCard = (string)reader["IdCard"];
if (reader["RealName"] != DBNull.Value) newEntity.RealName = (string)reader["RealName"];
}
return newEntity;
}

/// <summary>
/// 查询1行数据
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
public static Student GetEntity(Guid studentId)
{
Student entity = null;
string sql = @"select * from [Student] where [StudentId]=@StudentId";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = studentId;
conn.Open();
using (SqlDataReader sr = cmd.ExecuteReader())
{
while (sr.Read())
{
entity = Reader(sr);
}
}
}
}
return entity;
}

/// <summary>
/// 查询n行数据
/// </summary>
/// <returns></returns>
public static List<Student> SearchAllList()
{
List<Student> list = new List<Student>();
string sql = @"select * from [Student]";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader sr = cmd.ExecuteReader())
{
while (sr.Read())
{
Student info = Reader(sr);
list.Add(info);
}
}
}
}
return list;
}

/// <summary>
/// 添加数据列表
/// </summary>
/// <param name="newEntityList"></param>
/// <param name="message"></param>
/// <returns></returns>
public static bool Insert(List<Student> newEntityList, out string message)
{
message = string.Empty;
int rows = ;
string sql = @"insert into [Student] ([StudentId],[StudentNo],[IdCard],[RealName]) values (@StudentId,@StudentNo,@IdCard,@RealName)";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
conn.Open(); SqlCommand cmd = conn.CreateCommand();
SqlTransaction tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Transaction = tran; try
{
//打开数据库连接后，循环执行insert语句。在循环过程中，如果没有出现异常，则提交事务；如果出现异常，则回滚事务。
foreach (var newEntity in newEntityList)
{
cmd.Parameters.Clear();//注意 #region MyRegion
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = newEntity.StudentId;
cmd.Parameters.Add("@StudentNo", SqlDbType.Int).Value = newEntity.StudentNo;
cmd.Parameters.Add("@IdCard", SqlDbType.NVarChar).Value = newEntity.IdCard ?? (object)DBNull.Value;
cmd.Parameters.Add("@RealName", SqlDbType.NVarChar).Value = newEntity.RealName ?? (object)DBNull.Value;
#endregion rows += cmd.ExecuteNonQuery();
}
message = rows.ToString();
tran.Commit();//提交事务
return true;
}
catch (Exception e)
{
message = e.Message;
tran.Rollback();//回滚事务
return false;
}
}
}

/// <summary>
/// 修改数据列表
/// </summary>
/// <param name="updateEntityList"></param>
/// <param name="message"></param>
/// <returns></returns>
public static bool Update(List<Student> updateEntityList, out string message)
{
message = string.Empty;
int rows = ;
string sql = @"update [Student] set [StudentNo]=@StudentNo,[IdCard]=@IdCard,[RealName]=@RealName where [StudentId]=@StudentId";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
conn.Open(); SqlCommand cmd = conn.CreateCommand();
SqlTransaction tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Transaction = tran; try
{
//打开数据库连接后，循环执行insert语句。在循环过程中，如果没有出现异常，则提交事务；如果出现异常，则回滚事务。
foreach (var newEntity in updateEntityList)
{
cmd.Parameters.Clear();//注意
#region MyRegion
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = newEntity.StudentId;
cmd.Parameters.Add("@StudentNo", SqlDbType.Int).Value = newEntity.StudentNo;
cmd.Parameters.Add("@IdCard", SqlDbType.NVarChar).Value = newEntity.IdCard ?? (object)DBNull.Value;
cmd.Parameters.Add("@RealName", SqlDbType.NVarChar).Value = newEntity.RealName ?? (object)DBNull.Value;
#endregion
rows += cmd.ExecuteNonQuery();
}
message = rows.ToString();
tran.Commit();//提交事务
return true;
}
catch (Exception e)
{
message = e.Message;
tran.Rollback();//回滚事务
return false;
}
}
}

/// <summary>
/// 删除数据列表
/// </summary>
/// <param name="studentIdList"></param>
/// <param name="message"></param>
/// <returns></returns>
public static bool Delete(List<Student> studentIdList, out string message)
{
message = string.Empty;
int rows = ;
string sql = @"delete from [Student] where [StudentId]=@StudentId";
using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
{
conn.Open(); SqlCommand cmd = conn.CreateCommand();
SqlTransaction tran = conn.BeginTransaction(); cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Transaction = tran; try
{
//打开数据库连接后，循环执行insert语句。在循环过程中，如果没有出现异常，则提交事务；如果出现异常，则回滚事务。
foreach (var studentId in studentIdList)
{
cmd.Parameters.Clear();//注意
cmd.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = studentId;
rows += cmd.ExecuteNonQuery();
}
message = rows.ToString();
tran.Commit();//提交事务
return true;
}
catch (Exception e)
{
message = e.Message;
tran.Rollback();//回滚事务
return false;
}
}
}