C#中Sql数据库SQLHelper类的示例代码

发布时间:2021-03-06 14:09:35 作者:小新
来源:亿速云 阅读:148

这篇文章主要介绍C#中Sql数据库SQLHelper类的示例代码,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!

using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Data.SqlClient;using System.Data;using System.Configuration;
    public class SQLHelper    {        //取得数据库连接web.config 中配置         public static readonly string ConnectString = ConfigurationManager.ConnectionStrings["DBString"].ConnectionString;
/// <summary>        /// 无事务,数据查询        /// </summary>        /// <param name="cmdType">存储过程或Sql语句</param>        /// <param name="cmdText">存储过程名或Sql语句内容</param>        /// <param name="CommandParams">参数列表</param>        /// <returns></returns>        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnectString);
try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);                int val = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return val;            }            catch            {                throw;            }            finally            {                conn.Close();
}
}
        /// <summary>        /// 有事务,数据操作类        /// </summary>        /// <param name="trans">事务</param>        /// <param name="cmdType">操作类别 (stored procedure,sql)</param>        /// <param name="cmdText">存储过程名或Sql语句</param>        /// <param name="CommandParams">参数</param>        /// <returns>返回影响的数据行数</returns>        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {
SqlCommand cmd = new SqlCommand();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);            /*if (cmdType == CommandType.StoredProcedure)            {                cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;                cmd.ExecuteNonQuery();
val = (int)cmd.Parameters["@RETURN_VALUE"].Value;            }            else                if (cmdType==CommandType.Text)                 {                   val = cmd.ExecuteNonQuery();                  }*/            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();
return val;        }
/// <summary>        /// 返回数据集 DataReader        /// </summary>        /// <param name="cmdType"></param>        /// <param name="cmdText"></param>        /// <param name="CommandParams"></param>        /// <returns></returns>        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();            SqlConnection conn = new SqlConnection(ConnectString);
try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                cmd.Parameters.Clear();                return rdr;            }            catch (Exception ex)            {                conn.Close();                //   throw new Exception("操作失败!");                throw new Exception(ex.Message);            }        }
/// <summary>        /// 有事务的取数据        /// </summary>        /// <param name="trans"></param>        /// <param name="cmdType"></param>        /// <param name="cmdText"></param>        /// <param name="CommandParams"></param>        /// <returns></returns>        public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            return rdr;        }
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnectString);
try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);                object val = cmd.ExecuteScalar();                cmd.Parameters.Clear();                return val;            }            catch            {                throw;            }            finally            {                conn.Close();            }
}
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);            object val = cmd.ExecuteScalar();            cmd.Parameters.Clear();            return val;
}
/// <summary>        /// 根据Sql语句取得表        /// </summary>        /// <param name="cmdType"></param>        /// <param name="cmdText"></param>        /// <param name="CommandParams"></param>        /// <returns></returns>        public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            DataTable temptable = new DataTable();
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnectString);
try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder scb = new SqlCommandBuilder(da);
da.Fill(temptable);            }            finally            {                conn.Close();            }
return temptable;        }
        public static DataTable ExecuteTable(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            DataTable temptable = new DataTable();            SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);            SqlDataAdapter da = new SqlDataAdapter(cmd);            SqlCommandBuilder scb = new SqlCommandBuilder(da);            da.Fill(temptable);            cmd.Parameters.Clear();
return temptable;
}
        /// <summary>        /// 根据Sql语句或存储过程取得数据        /// </summary>        /// <param name="cmdType"></param>        /// <param name="cmdText"></param>        /// <param name="CommandParams"></param>        /// <returns></returns>        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlConnection conn = new SqlConnection(ConnectString);
SqlCommand cmd = new SqlCommand();
DataSet TempDataSet = new DataSet();
try            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);                SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);                sqlAdapter.Fill(TempDataSet);                cmd.Parameters.Clear();                return TempDataSet;
}            finally            {                conn.Close();            }
}
        public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)        {            SqlCommand cmd = new SqlCommand();            DataSet TempDataSet = new DataSet();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);            SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);            sqlAdapter.Fill(TempDataSet);            cmd.Parameters.Clear();            return TempDataSet;        }
/// <summary>        /// 生成Sql语句或准备        /// </summary>        /// <param name="cmd"></param>        /// <param name="conn"></param>        /// <param name="trans"></param>        /// <param name="cmdType"></param>        /// <param name="cmdText"></param>        /// <param name="cmdParms"></param>        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)        {
if (conn.State != ConnectionState.Open)                conn.Open();
cmd.Connection = conn;            cmd.CommandText = cmdText;
if (trans != null)                cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)            {                foreach (SqlParameter parm in cmdParms)                    cmd.Parameters.Add(parm);            }        }
public static object ToDBValue(object value)        {            return value == null ? DBNull.Value : value;        }
public static object FromDBValue(object dbValue)        {            return dbValue == DBNull.Value ? null : dbValue;        }    }

以上是“C#中Sql数据库SQLHelper类的示例代码”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注亿速云行业资讯频道!

推荐阅读:
  1. sqlhelper的编写
  2. WPF中SqlHelper

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sqlhelper csharp

上一篇:C#5.0中async和await关键字的用法

下一篇:c#中Noto Sans字体支持韩文的方法

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》