您的当前位置: 源码爱好者 » 网页制作教程(编程教程) » C#技术教程

通用的C# SQLServer操作类,支持存储过程

  • 标签:数据库操作类 数据库连接类  更新时间:2013-11-04
  •  通用DBHelper SQLServer数据库连接类,操作SQLServer的常用命令类,采用* McXiAo扩充封装,本类功能支持存储过程,包括有添加、删除、更新等操作的常用SQL语句执行等。

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    namespace GameCard.DAL
    {
       public static class DBHelper
       {
            private  static SqlConnection connection;
            public static SqlConnection Connection
            {  
                get {
                    string connectionstring =ConfigurationManager.ConnectionStrings["sqldatabase"].ConnectionString;
                    if (connection == null)
                    {
                        connection = new SqlConnection(connectionstring);
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                         connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                    return connection;
                }
               
            }
           public static int ExecuteCommand(string sql)
           {
               SqlCommand com = new SqlCommand(sql, Connection);
               int result= com.ExecuteNonQuery();
               return result;
           }
           public static int ExecuteCommand(string sql, params SqlParameter[] values)
           {
             SqlCommand com = new SqlCommand(sql, Connection);
             com.Parameters.AddRange(values);
             return com.ExecuteNonQuery();
           }
           public static int GetScalar(string sql)
           {
               SqlCommand com = new SqlCommand(sql,Connection);
               int result = int.Parse(com.ExecuteScalar().ToString());
               return  result;
           }
           public static string GetScala(string sql)
           {
               SqlCommand com = new SqlCommand(sql, Connection);
             string result = Convert.ToString(com.ExecuteScalar().ToString());
               return result;
    
           }
           public static int GetScalar(string sql, params SqlParameter[] values)
           {
               SqlCommand cmd = new SqlCommand(sql, Connection);
               cmd.Parameters.AddRange(values);
               int result = Convert.ToInt32(cmd.ExecuteScalar());
               return result;
           }
           public static DataTable GetDataSet(string sql)
           {
               DataSet dataset = new DataSet();
               SqlCommand com = new SqlCommand(sql, Connection);
               SqlDataAdapter da = new SqlDataAdapter(com);
               da.Fill(dataset);
               return dataset.Tables[0];
           }
           public static DataTable GetDataSet(string sql, params SqlParameter[] values)
           {
               DataSet dataset = new DataSet();
               SqlCommand com = new SqlCommand(sql, Connection);
               com.Parameters.AddRange(values);
               SqlDataAdapter da = new SqlDataAdapter(com);
               da.Fill(dataset);
               return dataset.Tables[0];
           }
           public static SqlDataReader GetReader(string sql)
           {
               SqlCommand com = new SqlCommand(sql, Connection);
               SqlDataReader reader = com.ExecuteReader();
               return reader;
           }
           public static SqlDataReader GetReader(string sql,params SqlParameter[] values)
           {
               SqlCommand com = new SqlCommand(sql, Connection);
               com.Parameters.AddRange(values);
               SqlDataReader reader=com.ExecuteReader();
               return reader;
           }
           public static int ExecuteProcCommand(string proc, params SqlParameter[] values)
           {
               SqlCommand cmd = new SqlCommand(proc, Connection);
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
               cmd.Parameters.AddRange(values);
               int i = 0;
               SqlParameter pamReturn = cmd.Parameters.Add("@returnVal", SqlDbType.Int);
               pamReturn.Direction = ParameterDirection.ReturnValue;
               cmd.ExecuteNonQuery();
               if (pamReturn.Value.ToString() != null)
               {
                   i = int.Parse(pamReturn.Value.ToString());
               }
               return i;
           }
           public static int GetProcScalar(string proc, params SqlParameter[] values)
           {
               SqlCommand cmd = new SqlCommand(proc, Connection);
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
               cmd.Parameters.AddRange(values);
    
               int result = Convert.ToInt32(cmd.ExecuteScalar());
               return result;
           }
           public static SqlDataReader GetProcReader(string proc, params SqlParameter[] values)
           {
               SqlCommand cmd = new SqlCommand(proc, Connection);
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
               cmd.Parameters.AddRange(values);
               SqlDataReader reader = cmd.ExecuteReader();
               return reader;
           }
           public static DataTable GetProcDataSet(string safeSql)
           {
               DataSet ds = new DataSet();
               SqlCommand cmd = new SqlCommand(safeSql, Connection);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               da.Fill(ds);
               return ds.Tables[0];
           }
           /// 存储过程
           public static DataTable GetProcDataSet(string proc, params SqlParameter[] values)
           {
               DataSet ds = new DataSet();
    
               SqlCommand cmd = new SqlCommand(proc, Connection);
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
               cmd.Parameters.AddRange(values);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
    
               da.Fill(ds);
               return ds.Tables[0];
           }
        }
    }

    您可将其保存为DBHelper.cs。

  • 已读: 次 收藏本文关闭本文打印本文复制链接