clsdb.cs

通过配置数据库连接并选择数据库表,可自动生成数据库表的实体类,并且实体类中包含记录集合,可在类中对各记录集合进行修改后一次性提交保存,支持事务.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

namespace DBProvider
{
    public class clsDB
    {
        private static SqlConnection mscnData;
        private static string mstrServer;
        private static string mstrDB;
        private static string mstrUser;
        private static string mstrPW;
        private static SqlCommand mscmData;
        private static SqlDataAdapter msdaData;
        private static SqlTransaction mstsTrans;
        private static bool mbolIsInTrans=false;

        private clsDB(){}

        public static SqlConnection Connection
        {
            get
            {
                if (!CheckConnect())
                {
                    return null;
                }
                return mscnData;
            }
        }

        public static string ServerName
        {
            get
            {
                return mstrServer;
            }
            set
            {
                mstrServer = value;
            }
        }

        public static string DBName
        {
            get
            {
                return mstrDB;
            }
            set
            {
                mstrDB = value;
            }
        }

        public static string UserName
        {
            get
            {
                return mstrUser;
            }
            set
            {
                mstrUser = value;
            }
        }

        public static string PassWord
        {
            get
            {
                return mstrPW;
            }
            set
            {
                mstrPW = value;
            }
        }

        public static DataTable GetData(string sql)
        {
            if (!CheckConnect())
            {
                return null;
            }

            DataTable dtbResult = new DataTable();
            mscmData.Parameters.Clear();
            mscmData.CommandText = sql;
            try
            {
                msdaData.Fill(dtbResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            return dtbResult;
        }

        public static bool ExeSql(string sql)
        {
            if (!CheckConnect())
            {
                return false;
            }

            try
            {
                if (mscnData.State!=ConnectionState.Open)
                mscnData.Open();
                mscmData.CommandText = sql;
                mscmData.ExecuteNonQuery();
                if (!mbolIsInTrans)
                {
                    mscnData.Close();
                }
            }
            catch (Exception ex)
            {
                if (mbolIsInTrans)
                {
                    mstsTrans.Rollback();
                }
                MessageBox.Show(ex.Message);
                return false;
            }
            return true;
        }

        public static bool ExeSql(string sql, List<SqlParameter> paras)
        {
            mscmData.Parameters.Clear();
            foreach(SqlParameter para in paras)
            {
                mscmData.Parameters.Add(para);
            }

            return ExeSql(sql);
        }

        public static DataTable GetTables()
        {
            if (!CheckConnect())
            {
                return null;
            }

            mscnData.Open();
            DataTable dtbTables=  mscnData.GetSchema("Tables",new string[] {null,null,null,"base table"});
            mscnData.Close();
            return dtbTables;
        }

        private static bool CheckConnect()
        {
            if (mscnData == null)
            {
                try
                {
                    if (mstrServer == null && mstrDB == null && mstrUser == null && mstrPW == null)
                    {
                        mstrServer = "23.36.2.203";
                        mstrDB = "yuanlinju";
                        mstrUser = "sa";
                        mstrPW = "My525Just";
                    }
                    string strCnt = "Data Source=" + mstrServer;
                    strCnt = strCnt + ";Initial Catalog=" + mstrDB;
                    strCnt = strCnt + ";Persist Security Info=True;User ID=" + mstrUser;
                    strCnt = strCnt + ";Password=" + mstrPW;
                    mscnData = new SqlConnection(strCnt);
                }
                catch
                {
                    MessageBox.Show("登录数据库失败，请与管理员联系。");
                    return false;
                }
            }

            if (mscmData == null)
            {
                mscmData = new SqlCommand();
                mscmData.Connection = mscnData;
                mscmData.CommandType = CommandType.Text;
            }

            if (msdaData == null)
            {
                msdaData = new SqlDataAdapter();
                msdaData.SelectCommand = mscmData;
            }

            //if (mstsTrans == null)
            //{
            //    //mstsTrans=
            //}
            return true;
        }

        //输入窗体，是否是新增，表名与ID序列
        //各表之间用"*"号分隔,表名与ID之间用","分隔
        public static bool SaveFormData(Control DataForm, bool IsAddNew,string Tables)
        {
            DataTable SaveTable;
            DataRow rowData;
            string strCol;
            string strVal;
            string[] strTables = Tables.Split("*".ToCharArray());
            string strID;
            string strSql;
            string strTable;

            foreach (string strTableID in strTables)
            {
                strTable = strTableID.Split(",".ToCharArray())[0];
                strID = strTableID.Split(",".ToCharArray())[1];
                strSql = "select top 0 * from " + strTable;
                SaveTable = GetData(strSql);
                //			bool bolIsAdd=false;
                if (SaveTable.Rows.Count == 0)
                {
                    SaveTable.Rows.Add(SaveTable.NewRow());
                    //				bolIsAdd=true;
                }
                rowData = SaveTable.Rows[0];
                rowData.BeginEdit();
                foreach (Control ctlTmp in DataForm.Controls)
                {
                    if (ctlTmp.Tag != null)
                    {
                        if (ctlTmp.Tag.ToString() != "")
                        {
                            strCol = ctlTmp.Tag.ToString().Replace("*", "").Replace("!", "");
                            switch (ctlTmp.GetType().ToString())
                            {
                                case "System.Windows.Forms.TextBox":
                                    {
                                        strVal = ctlTmp.Text;
                                        break;
                                    }
                                case "System.Windows.Forms.Label":
                                    {
                                        strVal = ctlTmp.Text;
                                        break;
                                    }
                                case "System.Windows.Forms.CheckBox":
                                    {
                                        if (((CheckBox)ctlTmp).Checked)
                                        {
                                            strVal = "1";
                                        }
                                        else
                                        {
                                            strVal = "0";
                                        }
                                        break;
                                    }
                                case "System.Windows.Forms.ComboBox":
                                    {
                                        if (ctlTmp.Tag.ToString().IndexOf("!") < 0)
                                        {
                                            strVal = ((ComboBox)ctlTmp).SelectedValue.ToString();
                                        }
                                        else
                                        {
                                            strVal = ((ComboBox)ctlTmp).Text;
                                        }
                                        break;
                                    }
                                case "System.Windows.Forms.ListBox":
                                    {
                                        strVal = ((ListBox)ctlTmp).ValueMember.ToString();
                                        break;
                                    }
                                case "System.Windows.Forms.DateTimePicker":
                                    {
                                        if (((DateTimePicker)ctlTmp).Checked)
                                        {
                                            strVal = ((DateTimePicker)ctlTmp).Value.ToString("yyyy-MM-dd HH:mm:ss");
                                        }
                                        else
                                        {
                                            strVal = null;
                                        }