[C#]数据库操作封装类

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections;

数据库操作类#region 数据库操作类
/**//// <summary>
/// <c>『钙娃儿 封装』</c>
/// <c>数据库操作类</c>
/// </summary>
public class csData
...{
    private SqlConnection conn;
    private SqlCommand cmd;
    private SqlDataAdapter ada;
    private DataSet ds;
    private string connstr;
    private SqlTransaction st;

    关闭数据库连接#region 关闭数据库连接
    /**//// <summary>
    /// <c>关闭数据库连接</c>
    /// </summary>
    private void closeDB()
    ...{
        if (conn.State == ConnectionState.Open)
        ...{
            conn.Close();
        }
    }
    #endregion

    初始化连接字符串#region 初始化连接字符串
    /**//// <summary>
    /// <c>『钙娃儿 封装』</c>
    /// <c>初始化连接字符串</c>
    /// </summary>
    public csData()
    ...{
        int x = WebConfigurationManager.AppSettings.Count;
        SqlConnection conn = null;
        try
        ...{

            for (int i = 0; i < x; i++)
            ...{
                connstr = WebConfigurationManager.AppSettings[i].ToString();
                conn = new SqlConnection(connstr);
                conn.Open();
                if (conn.State == ConnectionState.Open)
                ...{
                    break;
                }
            }
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            if (conn.State == ConnectionState.Open)
            ...{
                conn.Close();
            }
        }
    }
    #endregion

    返回DataSet的查询函数#region 返回DataSet的查询函数
    /**//// <summary>
    /// 传入Hashtable，输出DataSet
    /// </summary>
    /// <param name="ht">如果是ｓｑｌ语句，ｋｅｙ为临时表名，ｖａｌｕｅ为ｓｑｌ语句；如果是存储过程，ｋｅｙ为过程名，ｖａｌｕｅ为过程的参数数组</param>
    /// <returns>返回ＤａｔａＳｅｔ</returns>
    public DataSet getData(Hashtable ht) 
    ...{
        DataSet ds = null;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand();
            cmd.Connection = conn;
            ada = new SqlDataAdapter(cmd);
            ds = new DataSet();
            conn.Open();
            if (ht != null)
            ...{
                IDictionaryEnumerator ide = ht.GetEnumerator();
                while (ide.MoveNext())
                ...{
                    if (ide.Value != null)//有参数的存储过程或者ｓｑｌ语句
                    ...{
                        if (ide.Value.GetType() == Type.GetType("System.String"))//ｓｑｌ语句
                        ...{
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = ide.Value.ToString();
                            ada.Fill(ds, ide.Key.ToString());
                        }
                        else//有参数的存储过程
                        ...{
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = ide.Key.ToString();
                            foreach (SqlParameter sp in (SqlParameter[])ide.Value)
                            ...{
                                cmd.Parameters.Add(sp);
                            }
                            ada.Fill(ds, ide.Key.ToString());
                        }
                    }
                    else//没有参数的存储过程
                    ...{
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = ide.Key.ToString();
                        cmd.Parameters.Clear();
                        ada.Fill(ds, ide.Key.ToString());
                    }
                }
            }
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            closeDB();
        }
        return ds;
    }
    #endregion

    进行增删改操作#region 进行增删改操作
    /**//// <summary>
    /// 传入sql语句，执行增删改
    /// </summary>
    /// <param name="sql">增删改的sql</param>
    /// <returns>成功返回：1；失败返回：-1</returns>
    public int runSql(string sql)
    ...{
        int returnValue = -1;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            returnValue = 1;
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            conn.Close();
        }
        return returnValue;
    }
    #endregion

    进行增删改操作#region 进行增删改操作
    /**//// <summary>
    /// 传入存储过程和参数，执行增删改
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="paras">参数数组</param>
    /// <returns>成功返回：1；失败返回：-1</returns>
    public int runSql(string procName, params SqlParameter[] paras)
    ...{
        int dt = -1;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = conn;
            if (paras != null)
            ...{
                foreach (SqlParameter sp in paras)
                ...{
                    cmd.Parameters.Add(sp);
                }
            }
            conn.Open();
            cmd.ExecuteNonQuery();
            dt = 1;
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            closeDB();
        }
        return dt;
    }
    #endregion

    进行增删改操作#region 进行增删改操作
    /**//// <summary>
    /// 传入sql语句或者存储过程，执行增删改
    /// </summary>
    /// <param name="ht">封装sql语句或者存储过程的Hashtable</param>
    /// <returns>成功返回：1；失败返回：-1</returns>
    public int runSql(Hashtable ht)
    ...{
        int ds = -1;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            st = conn.BeginTransaction();
            cmd.Transaction = st;
            if (ht != null)
            ...{
                IDictionaryEnumerator ide = ht.GetEnumerator();
                while (ide.MoveNext())
                ...{
                    if (ide.Value != null)//有参数的存储过程或者ｓｑｌ语句
                    ...{
                        if (ide.Value.GetType() == Type.GetType("System.String"))//ｓｑｌ语句
                        ...{
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = ide.Value.ToString();
                            cmd.ExecuteNonQuery();
                        }
                        else//有参数的存储过程
                        ...{
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = ide.Key.ToString();
                            foreach (SqlParameter sp in (SqlParameter[])ide.Value)
                            ...{
                                cmd.Parameters.Add(sp);
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else//没有参数的存储过程
                    ...{
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = ide.Key.ToString();
                        cmd.Parameters.Clear();
                        cmd.ExecuteNonQuery();
                    }
                }
                st.Commit();
                ds = 1;
            }
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
            st.Rollback();
        }
        finally
        ...{
            closeDB();
        }
        return ds;
    }
    #endregion

    返回DataTable的查询函数#region 返回DataTable的查询函数
    /**//// <summary>
    ///函数输入SQL语句和临时表名，输出一个结果集（DATATABLE）
    /// </summary>
    /// <param name="sql">传入的sql语句</param>
    /// <param name="tmpTab">临时表名</param>
    /// <returns>返回DataTable</returns>
    public DataTable getData(string sql, string tmpTab)
    ...{
        DataTable dt = null;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand(sql, conn);
            ada = new SqlDataAdapter(cmd);
            ds = new DataSet();
            conn.Open();
            ada.Fill(ds, tmpTab);
            dt = ds.Tables[tmpTab];
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            closeDB();
        }
        return dt;
    }
    #endregion

    返回DataTable的查询函数#region 返回DataTable的查询函数
    /**//// <summary>
    ///函数输入存储过程，输出一个结果集（DATATABLE）
    /// </summary>
    /// <param name="procName">传入的sql语句</param>
    /// <param name="paras">存储过程的参数数组</param>
    /// <returns>返回DataTable</returns>
    public DataTable getData(string procName, params SqlParameter[] paras)
    ...{
        DataTable dt = null;
        try
        ...{
            conn = new SqlConnection(connstr);
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = conn;
            ds = new DataSet();
            if (paras != null)
            ...{
                foreach (SqlParameter sp in paras)
                ...{
                    cmd.Parameters.Add(sp);
                }
            }
            ada = new SqlDataAdapter(cmd);
            conn.Open();
            ada.Fill(ds, procName);
            dt = ds.Tables[procName];
        }
        catch (Exception e)
        ...{
            Console.WriteLine(e.Message);
        }
        finally
        ...{
            closeDB();
        }
        return dt;
    }
    #endregion
}
#endregion