文章代码如下：
class Program
{
static void Main (string[] args)
{
//连接数据库
string connString = "server=.;database=Student;user id=sa;pwd=123456";
Console.WriteLine (ADOUtils.ConnDB (connString)); //查询张三的数据
SqlParameter[] parameters = new[] { new SqlParameter ("@sname", "李四"), };
Task<List<StudentModel>> studentInfo = ADOUtils.SelectDBAsync<StudentModel> ("select SID,SName,SGender from StudentInfo where SName=@sname ", parameters);
studentInfo.ContinueWith((result) =>
{
if (studentInfo != null)
foreach (StudentModel studentModel in result.Result)
{
Console.WriteLine(studentModel);
}
else
{
Console.WriteLine("未查询到数据");
}
}); Thread.Sleep(4000); //SqlParameter[] parameters1 = new[] { new SqlParameter ("@gone", 1), new SqlParameter ("@gtwo", 2), };
////将两个人的性别对换。
//string sqlone = "update StudentInfo set SGender=@gone where SID=20200001";
//string sqltwo = "update StudentInfo set SGender=@gtwo where SID=20200002";
//ADOUtils.ExcuteForTransaction (new[] { sqlone, sqltwo }, parameters1); }
} static class ADOUtils
{
private static SqlConnection connection = null; /// <summary>
/// 连接数据库
/// </summary>
/// <param name="connString">数据库连接字符串</param>
/// <returns>是否连接成功 bool</returns>
public static bool ConnDB (string connString)
{
try
{
connection = new SqlConnection (connString);
connection.Open ();
return true;
}
catch
{
connection = null;
return false;
}
} /// <summary>
/// 断开连接
/// </summary>
public static void CloseConnect ()
{
connection.Close ();
connection.Dispose ();
} /// <summary>
/// 执行增，删，改操作
/// </summary>
/// <param name="sql">sal语句</param>
/// <param name="parameters">参数</param>
/// <returns>受影响的行数</returns>
public static int ExcuteSQL (string sql, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return 0;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} return command.ExecuteNonQuery ();
}
catch
{
return 0;
}
}
} /// <summary>
/// 执行聚合函数操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>聚合结果，如果执行出错，返回false</returns>
public static object ExcuteMethods (string sql, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return 0;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} return command.ExecuteScalar ();
}
catch
{
return false;
}
}
} /// <summary>
/// 执行查询操作(泛型版)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>数据集合，出错返回null</returns>
public static List<T> SelectDB<T> (string sql, SqlParameter[] parameters) where T : new()
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return null;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} SqlDataReader reader = command.ExecuteReader ();
if (reader.HasRows)
{
List<T> data = new List<T> ();
Type type = typeof (T);
object o = Activator.CreateInstance (type);
while (reader.Read ())
{
foreach (var property in type.GetProperties ())
{
property.SetValue (o, reader[property.Name]);
}
data.Add ((T)o);
}
reader.Close ();
return data;
} return null;
}
catch
{
return null;
}
}
} /// <summary>
/// 执行查询操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>数据集合，出错返回null</returns>
public static List<StudentModel> SelectStudentInfo (string sql, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return null;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} SqlDataReader reader = command.ExecuteReader ();
if (reader.HasRows)
{
List<StudentModel> data = new List<StudentModel> ();
while (reader.Read ())
{
StudentModel sm = new StudentModel ();
sm.SID = reader.GetInt32 (0);
sm.SName = reader.GetString (1);
sm.SGender = reader.GetInt32 (2);
data.Add (sm);
}
reader.Close ();
return data;
} return null;
}
catch
{
return null;
}
}
} /// <summary>
/// 使用事务执行多个增删改任务
/// </summary>
/// <param name="sqls">多个sql语句</param>
/// <param name="parameters">多个sql语句共用的参数</param>
/// <returns>返回受影响的总行数</returns>
public static int ExcuteForTransaction (string[] sqls, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return 0;
}
using (SqlCommand command = connection.CreateCommand ())
{
using (SqlTransaction transaction = connection.BeginTransaction ())
{
try
{
int count = 0;
command.Transaction = transaction; if (parameters != null)
command.Parameters.AddRange (parameters); foreach (string sql in sqls)
{
command.CommandText = sql;
count += command.ExecuteNonQuery ();
}
transaction.Commit ();
Console.WriteLine ("事务提交");
return count;
}
catch (Exception exception)
{
Console.WriteLine (exception.Message);
Console.WriteLine ("事务回滚");
transaction.Rollback ();
return 0;
}
}
}
} /// <summary>
/// 执行增，删，改操作(异步版)
/// </summary>
/// <param name="sql">sal语句</param>
/// <param name="parameters">参数</param>
/// <returns>受影响的行数</returns>
public static async Task<int> ExcuteAsync (string sql, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return 0;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} return await command.ExecuteNonQueryAsync ();
}
catch
{
return 0;
}
}
} /// <summary>
/// 执行查询操作(异步泛型版)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>数据集合，出错返回null</returns>
public static async Task<List<T>> SelectDBAsync<T> (string sql, SqlParameter[] parameters) where T : new()
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return null;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} SqlDataReader reader = await command.ExecuteReaderAsync ();
if (reader.HasRows)
{
List<T> data = new List<T> ();
Type type = typeof (T);
object o = Activator.CreateInstance (type);
while (reader.Read ())
{
foreach (var property in type.GetProperties ())
{
property.SetValue (o, reader[property.Name]);
}
data.Add ((T)o);
}
reader.Close ();
return data;
} return null;
}
catch
{
return null;
}
}
} /// <summary>
/// 执行聚合函数操作（异步版）
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>聚合结果，如果执行出错，返回false</returns>
public static async Task<object> ExcuteMethodsAsync (string sql, SqlParameter[] parameters)
{
if (connection == null)
{
Console.WriteLine ("数据库未连接");
return 0;
} using (SqlCommand command = new SqlCommand (sql, connection))
{
try
{
if (parameters != null)
{
command.Parameters.AddRange (parameters);
} return await command.ExecuteScalarAsync ();
}
catch
{
return false;
}
}
} } class StudentModel
{
public int SID { get; set; }
public string SName { get; set; }
public int SGender { get; set; } public override string ToString ()
{
return $"SID:{SID}\tSName:{SName}\tSGender:{SGender}";
}
}