使用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> studentInfo = ADOUtils.SelectDBAsync ("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;

        /// 

        /// 连接数据库
        /// 
        /// 数据库连接字符串
        /// 是否连接成功 bool
        public static bool ConnDB (string connString)
        {
            try
            {
                connection = new SqlConnection (connString);
                connection.Open ();
                return true;
            }
            catch
            {
                connection = null;
                return false;
            }
        }

        /// 

        /// 断开连接
        /// 
        public static void CloseConnect ()
        {
            connection.Close ();
            connection.Dispose ();
        }

        /// 

        /// 执行增，删，改操作
        /// 
        /// sal语句
        /// 参数
        /// 受影响的行数
        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;
                }
            }
        }

        /// 

        /// 执行聚合函数操作
        /// 
        /// sql语句
        /// 参数
        /// 聚合结果，如果执行出错，返回false
        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;
                }
            }
        }

        /// 

        /// 执行查询操作(泛型版)
        /// 
        /// sql语句
        /// 参数
        /// 数据集合，出错返回null
        public static List SelectDB (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 data = new List ();
                        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;
                }
            }
        }

        /// 

        /// 执行查询操作
        /// 
        /// sql语句
        /// 参数
        /// 数据集合，出错返回null
        public static List 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 data = new List ();
                        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;
                }
            }
        }

        /// 

        /// 使用事务执行多个增删改任务
        /// 
        /// 多个sql语句
        /// 多个sql语句共用的参数
        /// 返回受影响的总行数
        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;
                    }
                }
            }
        }

        /// 

        /// 执行增，删，改操作(异步版)
        /// 
        /// sal语句
        /// 参数
        /// 受影响的行数
        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;
                }
            }
        }

        /// 

        /// 执行查询操作(异步泛型版)
        /// 
        /// sql语句
        /// 参数
        /// 数据集合，出错返回null
        public static async Task> SelectDBAsync (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 data = new List ();
                        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;
                }
            }
        }

        /// 

        /// 执行聚合函数操作（异步版）
        /// 
        /// sql语句
        /// 参数
        /// 聚合结果，如果执行出错，返回false
        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}";
        }
    }