C# 连接SQLSERVER数据库常用操作类

复制代码
  /// <summary>
        /// 增删改操作类
        /// </summary>
        /// <param name="sql">执行的sql</param>
        /// <returns></returns>
        public static int ExecuteSql(string sql)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectStr))
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        return cmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 查询sql
        /// </summary>
        /// <param name="sql">查询语句sql</param>
        /// <returns></returns>
        public static DataSet Query(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection(connectStr))
                {
                    con.Open();
                    using (SqlDataAdapter cmd = new SqlDataAdapter(sql, con))
                    {
                        cmd.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 执行增删改查存储过程
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="parameter">存储过程参数</param>
        /// <returns></returns>
        public static int ExecuteProc(string ProcName, SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectStr))
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = ProcName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;

                        for (int i = 0; i < parameter.Length; i++)
                        {
                            cmd.Parameters.Add(parameter[i]);
                        }

                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 执行查询的存储过程
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="parameter">存储过程参数</param>
        /// <returns></returns>
        public static DataSet ExecuteProcQuery(string ProcName, SqlParameter[] parameter)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection(connectStr))
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = ProcName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;

                        for (int i = 0; i < parameter.Length; i++)
                        {
                            cmd.Parameters.Add(parameter[i]);
                        }

                        SqlDataAdapter dt = new SqlDataAdapter(cmd);
                        dt.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 执行带输出值的存储过程(返回值)
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="parameter">参数</param>
        /// <param name="RParamer">返回值名</param>
        /// <param name="direction">返回值参数类型</param>
        /// <returns>SDB_Helper.ExecuteProc("test",parameter,"count", ParameterDirection.Output);</returns>
        public static string ExecuteProc(string ProcName, SqlParameter[] parameter, string RParamer, ParameterDirection direction)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectStr))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = ProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < parameter.Length; i++)
                    {
                        cmd.Parameters.Add(parameter[i]);
                        if (parameter[i].ParameterName == "@" + RParamer)
                        {
                            cmd.Parameters[i].Direction = direction;
                        }
                    }
                    cmd.Connection = con;
                    SqlDataAdapter dt = new SqlDataAdapter(cmd);
                    dt.Fill(ds);

                    string tem = cmd.Parameters["@count"].Value.ToString();
                    return tem;
                }
            }
        }
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sqls">执行的所有sql语句</param>
        /// <returns></returns>
        public static int ExecuteTranction(string sqls)
        {

            try
            {
                using (SqlConnection con = new SqlConnection(connectStr))
                {
                    con.Open();

                    using (SqlTransaction transaction = con.BeginTransaction())
                    {


                        using (SqlCommand cmd = new SqlCommand())
                        {
                            try
                            {

                          
                            cmd.CommandText = sqls;
                            cmd.Connection = con;
                            cmd.Transaction = transaction;
                            int i = cmd.ExecuteNonQuery();
                            transaction.Commit();
                                return i;
                            }
                            catch (Exception)
                            {
                                transaction.Rollback();

                                return 0;
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                return 0;
            }
        }
复制代码

 

posted @   RC城  阅读(366)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示