C# WinForm 数据库连接及对数据库的相关操作

1.首先在App.config配置文件中配置数据库连接字符串：

<appSettings>
      <add key="connectionstring" value="server=XXX.XX.X.XX;uid=sa;pwd=******;database=XXXX"/>
      <!--<add key="datahost" value="XXX.XX.X.XX" />
      <add key="database" value="XXXX" />
      <add key="uid" value="sa" />
      <add key="pwd" value="******" />-->
    </appSettings>

2.代码获取配置文件中设置的数据库连接字符串

        private static string connectionString = ConfigurationManager.AppSettings["connectionstring"];

3.创建数据库连接方法：

 public static SqlConnection CreateSqlConn() {
            SqlConnection conn = new SqlConnection(connectionString);
            return conn;
        }

4.通过创建数据库操作类（SqlCommand），实现对数据库的增删改查操作：

增：

 public static int SaveWorkInfo(SqlConnection conn,string workName,string dealPeriod,string syncType) {
            if(conn.State == ConnectionState.Closed){
                conn.Open();
            }
            IDbTransaction tran = conn.BeginTransaction();
            int id = 0;
            try {
                string tableName = "HH_SyncWorkInfo";
                string column = "WorkName,DealPeriod,SyncType,CreatedTime";
                string value = "'" + workName + "','" + dealPeriod + "','" + syncType + "',getDate()";
                id = createPkReturnId(tableName,column,value,conn,tran);
                if(id > 0){
                    tran.Commit();
                    conn.Close();
                }
            }catch(Exception ex){
                tran.Rollback();
                conn.Close();
                throw new Exception("保存数据失败！具体详情：" + ex.Message.ToString());
            }
            return id;
        }


 private static int createPkReturnId(string tableName, string column, string value, SqlConnection conn, IDbTransaction tran)
        {
            try
            {
                String strSQL = "INSERT INTO " + tableName + " (" + column + ") VALUES (" + value + ");SELECT @@Identity";
                SqlCommand cmd = (SqlCommand)conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.Transaction = (SqlTransaction)tran;
                int id = Int32.Parse(cmd.ExecuteScalar().ToString());
                return id;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception("主表数据保存失败！具体详情：" + ex.Message.ToString());
            }
        }

删：

public static bool DeleteWorkItem(string ID)
        {
            SqlConnection conn = CreateSqlConn();
            conn.Open();
            IDbTransaction tran = conn.BeginTransaction();
            try
            {
                string strSQL = "delete HH_SyncWorkInfo where ID='" + ID + "'";
                int result = conn.Execute(strSQL, null, tran);
                if (result > 0)
                {
                    tran.Commit();
                    conn.Close();
                    return true;
                }
                else {
                    tran.Rollback();
                    conn.Close();
                    return false;
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw new Exception("删除失败！具体详情：" + ex.Message.ToString());
            }
        }

改：

public static bool UpdateWorkInfo(string id, string workName, string dealPeriod, string syncType)
        {
            using (var conn = CreateSqlConn())
            {
                string sql_update = "update HH_SyncWorkInfo SET WorkName = '" + workName + "',dealPeriod = '"+ dealPeriod + "',syncType= '"+ syncType +"' where ID = " + id;
                int result = conn.Execute(sql_update);

                if (result > 0)
                    return true;
                else
                    return false;
            }
        }

查：

 public static List<WorkInfo> GetWorksInfo()
        {
            using (var conn = CreateSqlConn())
            {
                string sql = "select * from HH_SyncWorkInfo";
                List<WorkInfo> list = conn.Query<WorkInfo>(sql).AsList();
                if (list.Count > 0)
                {
                    return list;
                }
                else
                {
                    return null;
                }
            }
        }

5.调用存储过程

/// <summary>
        /// 
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="parameters">参数数组</param>
        /// <returns></returns>
        public static string exectueProcude(string sql, params SqlParameter[] parameters) {
            using (SqlConnection conn = SqlHelperDB.CreateSqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(parameters);
                    string result = cmd.ExecuteScalar().ToString();
                    cmd.Parameters.Clear();
                    conn.Close();
                    return result;
                }
            }
        }

通过ExecuteScalar()返回执行结果第一行第一列的值。

在程序中调用执行存储过程方法：

SqlParameter[] para = new SqlParameter[1];
para[0] = new SqlParameter("@OrgID", org_list[nRow].FID);
string Max_CreateTime = ProviderDB.executeProcude("HH_P_GET_GetMax_CreateTime", para);