使用C#进行数据库增删改查ADO.NET(三)
文章代码如下：
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}"; } }
