数据库连接类

2019独角兽企业重金招聘Python工程师标准>>>

数据库连接 使用sql语句 进行查询、删除、修改、添加

#region 
        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        private static string sqlconn ="server=192.168.3.95;database=dbname;user=sa;password=123456789";
        #endregion
        /// <summary>
        /// 传入sql 返回相应数据表 查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="tableName">自定义表名</param>
        /// <returns></returns>
        public static DataTable GetDBData(string sql,string tableName)
        {
            DataTable dt = new DataTable();
            
            SqlConnection my_conn;
            try
            {
                my_conn = new SqlConnection(sqlconn);   //用SqlConnection对象与指定的数据库相连接
                my_conn.Open();  //打开数据库连接

                SqlDataAdapter SQLda = new SqlDataAdapter(sql, my_conn);  //创建一个SqlDataAdapter对象，并获取指定数据表的信息
                SQLda.SelectCommand.CommandTimeout = 1800; //设置为半个小时，超时则报错
                DataSet Ds = new DataSet(); //创建DataSet对象
                SQLda.Fill(Ds, tableName);  //通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中

                dt = Ds.Tables[tableName];

                if (my_conn.State == ConnectionState.Open)   //判断是否打开与数据库的连接
                {
                    my_conn.Close();   //关闭数据库的连接
                    my_conn.Dispose();   //释放My_con变量的所有空间
                }

            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("链接出错,请检查配置");
            }

            return dt;
        }

        /// <summary>
        /// 修改 删除 增加
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static Boolean GetUpdateRes(string sql) {
            Boolean resflag = false;

            SqlConnection my_conn;
            int num = 0;

            try
            {
                my_conn = new SqlConnection(sqlconn);   //用SqlConnection对象与指定的数据库相连接
                my_conn.Open();  //打开数据库连接
                
                SqlCommand SQLcom = new SqlCommand(sql, my_conn); //创建一个SqlCommand对象，用于执行SQL语句
                num = SQLcom.ExecuteNonQuery();   //执行SQL语句            
                SQLcom.Dispose();   //释放所有空间
                
                if (my_conn.State == ConnectionState.Open)   //判断是否打开与数据库的连接
                {
                    my_conn.Close();   //关闭数据库的连接
                    my_conn.Dispose();   //释放My_con变量的所有空间
                }

                if (num > 0) {
                    resflag = true;
                }
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("链接出错,请检查配置");
            }
            return resflag;
        }

数据库连接：使用存储过程

#region 通用存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            if (M_str_sqlcon == "")
            {
                M_str_sqlcon = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
            }
            using (SqlConnection connection = new SqlConnection(M_str_sqlcon))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = 60000; //超时设置
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        #endregion

        #region 存储过程参数转换
        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }

            return command;
        }
        #endregion

 

转载于:https://my.oschina.net/qingqingdego/blog/3006631