SQLHelper

 public class SQLHelp
    {
        private string Server = IniRead.IniReadWrite.ReadString("System", "server", "F");
        private string DataBase = IniRead.IniReadWrite.ReadString("System", "DataBase", "F");  
        private string UserName = IniRead.IniReadWrite.ReadString("System", "user", "F");      
        private string Password = IniRead.IniReadWrite.ReadString("System", "Password", "F"); 

        private string ConnectionString = "";

        public SQLHelp()
        {
            ConnectionString = "Data Source=" + Server + ";Initial Catalog=" + DataBase + ";User ID=" + UserName + ";Password=" + Password;
        }

        public string SQLServer
        {
            get { return Server; }
            set { Server = value; }
        }

        public string SQLDataBase
        {
            get { return DataBase; }
            set { DataBase = value; }
        }

        public string SQLConnectionString
        {
            get { return ConnectionString; }
            set { ConnectionString = value; }
        }

        public int ExecuteSQL(string SQLString)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQLString;

            int Count = NewSqlCommand.ExecuteNonQuery();

            return Count;
        }
        public string GetRecordString(string SQLString)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQLString;

            return NewSqlCommand.ExecuteScalar().ToString();
        }

        public int GetRecordCount(string SQLString)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQLString;

            int Count = Convert.ToInt32(NewSqlCommand.ExecuteScalar());

            return Count;
        }

        public SqlDataReader GetData(string SQLString)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQLString;

            SqlDataReader NewSqlDataReader = NewSqlCommand.ExecuteReader();

            return NewSqlDataReader;
        }

        public DataTable GetNewCaseTable()
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            string SQL = "select * from [Case] where IsDeleted = 0 and Status = '最新警情'";

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQL;

            SqlDataAdapter NewSqlDataAdapter = new SqlDataAdapter();
            NewSqlDataAdapter.SelectCommand = NewSqlCommand;

            DataTable NewDataTable = new DataTable();

            NewSqlDataAdapter.Fill(NewDataTable);

            return NewDataTable;
        }

        public DataTable GetCaseImage(string CaseCode)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            string SQL = "select * from CaseFile where CaseCode = '@CaseCode' and FileType = '图片' and IsDeleted = 0";
            SQL = SQL.Replace("@CaseCode", CaseCode);

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQL;

            SqlDataAdapter NewSqlDataAdapter = new SqlDataAdapter();
            NewSqlDataAdapter.SelectCommand = NewSqlCommand;

            DataTable NewDataTable = new DataTable();

            NewSqlDataAdapter.Fill(NewDataTable);

            return NewDataTable;
        }

        public DataTable GetPoliceCarInformation(string CarNumber)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            string SQL = "select * from PoliceCar where CarNumber = '@CarNumber' and IsDeleted = 0";
            SQL = SQL.Replace("@CarNumber", CarNumber);

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQL;

            SqlDataAdapter NewSqlDataAdapter = new SqlDataAdapter();
            NewSqlDataAdapter.SelectCommand = NewSqlCommand;

            DataTable NewDataTable = new DataTable();

            NewSqlDataAdapter.Fill(NewDataTable);

            return NewDataTable;
        }

        public DataTable GetRoadCameraInformation(string DeviceNumber)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            string SQL = "select * from RoadCamera where DeviceNumber = '@DeviceNumber' and IsDeleted = 0";
            SQL = SQL.Replace("@DeviceNumber", DeviceNumber);

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQL;

            SqlDataAdapter NewSqlDataAdapter = new SqlDataAdapter();
            NewSqlDataAdapter.SelectCommand = NewSqlCommand;

            DataTable NewDataTable = new DataTable();

            NewSqlDataAdapter.Fill(NewDataTable);

            return NewDataTable;
        }

        public DataTable GetDataTable(string SQLString)
        {
            SqlConnection NewSqlConnection = new SqlConnection();
            NewSqlConnection.ConnectionString = ConnectionString;
            NewSqlConnection.Open();

            SqlCommand NewSqlCommand = NewSqlConnection.CreateCommand();
            NewSqlCommand.CommandText = SQLString;

            SqlDataAdapter NewSqlDataAdapter = new SqlDataAdapter();
            NewSqlDataAdapter.SelectCommand = NewSqlCommand;

            DataTable NewDataTable = new DataTable();

            NewSqlDataAdapter.Fill(NewDataTable);

            return NewDataTable;
        }

        public static List<T> GetEntityFromDataTable<T>(DataTable sourceDT) where T : class
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            foreach (DataRow dRow in sourceDT.Rows)
            {
                object obj = Activator.CreateInstance(type);
                foreach (var prop in type.GetProperties())
                {
                    if (!dRow.Table.Columns.Contains(prop.Name))
                    {
                        //表中不包含Model的自定义字段
                        continue;
                    }
                    if (string.IsNullOrEmpty(dRow[prop.Name].ToString()))
                    {
                        prop.SetValue(obj, null, null);
                    }
                    else
                    {
                        prop.SetValue(obj, dRow[prop.Name], null);
                    }
                }
                list.Add(obj as T);
            }
            return list;
        }
    }