C# 连接SQL数据库

测试代码：控制台应用

using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace XF.ADO.DB
{

    public class T2
    {
        public int? fId { get; set; }
        public string fname { get; set; }
        public string fpass { get; set; }
        public DateTime? faddtime { get; set; }
        public string ftimespan { get; set; }
    }

    public class XFDB
    {

        static void Main()
        {
           
            //查询
            string connstr = "Data Source = 127.0.0.1; Initial Catalog = Test; Persist Security Info = True; User ID = sa; Password = xinfu978";
            XFDB db = new XFDB(connstr,false);
            Console.WriteLine(DateTime.Now.ToString());
            try
            {
                T2 d = new T2();
                //d.fId = 1058585;
                d.fname = "kkkkkkkkkk";
                d.faddtime = DateTime.Now;

                for (int i = 0; i < 1000000; i++)
                {
                    db.FnAdd(d);
                }


                //int i = db.FnUpdate(d, "fId", "T2");
                //d.fname = "qqqqqqqqq";
                //i = db.FnUpdate(d, "fId", "T2");
                //db.Commit();
            }
            catch (Exception e)
            {
                db.Rollback();
            }

            Console.WriteLine(DateTime.Now.ToString());
            db.FnDispost();

            Console.ReadKey();
        }


        string connectString = "";
        public XFDB(string connectString,bool isRran = false)
        {
            this.connectString = connectString;
            conn = new SqlConnection(connectString);
            conn.Open();
            if (isRran)
            {
                tran = conn.BeginTransaction();
            }
        }

        public void Commit()
        {
            if (tran != null)
            {
                tran.Commit();
            }
        }

        public void Rollback()
        {
            if (tran != null)
            {
                tran.Rollback();
            }
        }

        public void FnDispost()
        {
            try
            {
                conn.Dispose();
                tran.Dispose();
            }
            catch (Exception)
            {

            }
        }
        SqlConnection conn = null;
        SqlTransaction tran = null;
        
        public DataTable FnSelect(string sqlStr)
        {
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(sqlStr, conn);
                DataSet myDataSet = new DataSet();      // 创建DataSet
                myDataAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
        }

        public T FnSelect<T>(string sqlStr)
        {
            SqlDataAdapter myDataAdapter = new SqlDataAdapter(sqlStr, conn);
            DataSet myDataSet = new DataSet();      // 创建DataSet
            myDataAdapter.Fill(myDataSet);
            return FnDataTableToList<T>(myDataSet.Tables[0]);

        }

        public int FnExecute(string sqlStr)
        {
            SqlCommand cmd = conn.CreateCommand();
            if (tran != null)
            {
                cmd.Transaction = tran;
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlStr;   //sql语句
            return cmd.ExecuteNonQuery();

        }

        public int FnAdd<T>(T obj)
        {
            string classname = typeof(T).ToString();
            classname = classname.Substring(classname.LastIndexOf(".") + 1);
            return FnAdd<T>(obj, classname);
        }

        public int FnAdd<T>(T obj, string mTableName)
        {
            string key = "";
            string val = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            bool addDo = true;
            foreach (PropertyInfo i in ps)
            {
                Object objq = i.GetValue(obj, null);
                if (objq != null)
                {
                    if (addDo)
                    {
                        key += $"[{i.Name}]";
                        val += $"'{objq}'";
                        addDo = false;
                    }
                    else
                    {
                        key += $",[{i.Name}]";
                        val += $",'{objq}'";
                    }
                }
            }
            return FnExecute($"INSERT INTO [dbo].[{mTableName}] ({key}) VALUES ({val})");
        }

        public int FnUpdate<T>(T obj, string PId, string mTableName)
        {
            string changes = "";
            string where = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            bool addDo = true;
            foreach (PropertyInfo i in ps)
            {
                Object objq = i.GetValue(obj, null);
                if (i.Name == PId.ToString())
                {
                    if (objq.GetType().ToString().IndexOf("Int") >= 0)
                    {
                        where = $" WHERE [{PId}] = {objq}";
                    }
                    else
                    {
                        where = $" WHERE [{PId}] = '{objq}'";
                    }
                }
                else
                {
                    if (objq != null)
                    {
                        if (addDo)
                        {
                            changes += $"[{i.Name}] = '{objq}'";
                            addDo = false;
                        }
                        else
                        {
                            changes += $",[{i.Name}] = '{objq}'";
                        }
                    }
                }
            }
            return FnExecute($"UPDATE [dbo].[{mTableName}] set {changes} {where}");
        }

        public int FnUpdate<T>(T obj, int PId, string mTableName)
        {
            string changes = "";
            string where = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            bool addDo = true;
            foreach (PropertyInfo i in ps)
            {
                Object objq = i.GetValue(obj, null);
                if (i.Name == PId + "")
                {
                    where = $" WHERE [{PId}] = {objq}";
                    continue;
                }

                if (objq != null)
                {
                    if (addDo)
                    {
                        changes += $"[{i.Name}] = '{objq}'";
                        addDo = false;
                    }
                    else
                    {
                        changes += $",[{i.Name}] = '{objq}'";
                    }
                }
            }
            return FnExecute($"UPDATE [dbo].[{mTableName}] set {changes} {where}");
        }

        public string FnAdd2<T>(T obj, string mTableName)
        {
            string key = "";
            string val = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            bool addDo = true;
            foreach (PropertyInfo i in ps)
            {
                Object objq = i.GetValue(obj, null);
                if (objq != null)
                {
                    if (addDo)
                    {
                        key += $"[{i.Name}]";
                        val += $"'{objq}'";
                        addDo = false;
                    }
                    else
                    {
                        key += $",[{i.Name}]";
                        val += $",'{objq}'";
                    }
                }
            }
            return $"INSERT INTO [dbo].[{mTableName}] ({key}) VALUES ({val})";
        }

        public T FnDataTableToList<T>(DataTable dt)
        {
            return JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(dt, new DataTableConverter()));
        }

        public static object FnOutObjExitPar(object obj, string mOutPar)
        {
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            foreach (PropertyInfo i in ps)
            {
                if (mOutPar.IndexOf(i.Name) < 0)
                {
                    i.SetValue(obj, null);
                }
            }
            return obj;
        }

        static JsonSerializerSettings mJsonSettings = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };
        public static string ObjectToJson(object obj)
        {
            return JsonConvert.SerializeObject(obj, Formatting.None, mJsonSettings);
        }
        public static T JsonToObject<T>(string json)
        {
            return JsonConvert.DeserializeObject<T>(json);
        }
    }
}