C# SQL数据库操作通用类

最新推荐文章于 2023-07-03 12:49:18 发布

lchunl9707

最新推荐文章于 2023-07-03 12:49:18 发布

阅读量1.2k

点赞数

分类专栏：技术类文章标签： sql 数据库 c# dataset string exception

技术类专栏收录该内容

5 篇文章 0 订阅

订阅专栏

C# SQL数据库操作通用类

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace Framework.DataBase
{
///
/// 通用数据库类
///
public class DataBase
{

private string ConnStr = null;

public DataBase()
{
   ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
}
public DataBase(string Str)
{
   try
   {
    this.ConnStr = Str;

   }
   catch(Exception ex)
   {
    throw ex;
   }
}

///
/// 返回connection对象
///
///
public SqlConnection ReturnConn()
{
   SqlConnection Conn = new SqlConnection(ConnStr);
   Conn.Open();
   return Conn;
}
public void Dispose(SqlConnection Conn)
{
   if(Conn!=null)
   {
    Conn.Close();
    Conn.Dispose();
   }
}
///
/// 运行SQL语句
///
///
public void RunProc(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlCommand Cmd ;
   Cmd = CreateCmd(SQL, Conn);
   try
   {
    Cmd.ExecuteNonQuery();
   }
   catch
   {
    throw new Exception(SQL);
   }
   Dispose(Conn);
   return;
}

///
   /// 运行SQL语句返回DataReader
///
    ///
    /// SqlDataReader对象.
public SqlDataReader RunProcGetReader(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlCommand Cmd ;
   Cmd = CreateCmd(SQL, Conn);
   SqlDataReader Dr;
   try
   {
    Dr = Cmd.ExecuteReader(CommandBehavior.Default);
   }
catch
   {
    throw new Exception(SQL);
   }
   //Dispose(Conn);
   return Dr;
}

///
/// 生成Command对象
///
///
///
///
public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
   SqlCommand Cmd ;
   Cmd = new SqlCommand(SQL, Conn);
   return Cmd;
}

///
/// 生成Command对象
///
///
///
public SqlCommand CreateCmd(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlCommand Cmd ;
   Cmd = new SqlCommand(SQL, Conn);
   return Cmd;
}
///
/// 返回adapter对象
///
///
///
///
public SqlDataAdapter CreateDa(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlDataAdapter Da;
   Da = new SqlDataAdapter(SQL, Conn);
   return Da;
}

///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
public DataSet RunProc(string SQL ,DataSet Ds)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlDataAdapter Da;
   //Da = CreateDa(SQL, Conn);
   Da = new SqlDataAdapter(SQL,Conn);
   try
   {
    Da.Fill(Ds);
   }
   catch(Exception Err)
   {
    throw Err;
   }
   Dispose(Conn);
   return Ds;
}
///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
/// 表名
public DataSet RunProc(string SQL ,DataSet Ds,string tablename)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlDataAdapter Da;
   Da = CreateDa(SQL);
   try
   {
    Da.Fill(Ds,tablename);
   }
   catch(Exception Ex)
   {
    throw Ex;
   }
   Dispose(Conn);
   return Ds;
}

///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
/// 表名
public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string

tablename )
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlDataAdapter Da ;
   Da = CreateDa(SQL);
   try
   {
    Da.Fill(Ds, StartIndex, PageSize, tablename);
   }
   catch(Exception Ex)
   {
    throw Ex;
   }
Dispose(Conn);
   return Ds;
}

///
/// 检验是否存在数据
///
///
public bool ExistDate(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlDataReader Dr ;
   Dr = CreateCmd(SQL,Conn).ExecuteReader();
   if (Dr.Read())
   {
    Dispose(Conn);
    return true;
   }
   else
   {
    Dispose(Conn);
    return false;
   }
}

///
/// 返回SQL语句执行结果的第一行第一列
///
/// 字符串
public string ReturnValue(string SQL)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   string result;
   SqlDataReader Dr ;
   try
{
    Dr = CreateCmd(SQL,Conn).ExecuteReader();
    if (Dr.Read())
    {
     result = Dr[0].ToString();
     Dr.Close();
    }
    else
    {
     result = "";
     Dr.Close();
    }
   }
   catch
   {
    throw new Exception(SQL);
   }
   Dispose(Conn);
   return result;
}

///
/// 返回SQL语句第一列,第ColumnI列,
///
/// 字符串
public string ReturnValue(string SQL, int ColumnI)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   string result;
   SqlDataReader Dr ;
   try
   {
    Dr = CreateCmd(SQL,Conn).ExecuteReader();
   }
   catch
   {
    throw new Exception(SQL);
   }
   if (Dr.Read())
   {
    result = Dr[ColumnI].ToString();
   }
else
   {
    result = "";
   }
   Dr.Close();
   Dispose(Conn);
   return result;
}

///
/// 生成一个存储过程使用的sqlcommand.
///
/// 存储过程名.
/// 存储过程入参数组.
/// sqlcommand对象.
public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlCommand Cmd = new SqlCommand(procName, Conn);
   Cmd.CommandType = CommandType.StoredProcedure;
   if (prams != null)
   {
    foreach (SqlParameter parameter in prams)
    {
     if(parameter != null)
     {
      Cmd.Parameters.Add(parameter);
     }
    }
   }
   return Cmd;
}
///
/// 为存储过程生成一个SqlCommand对象
///
/// 存储过程名
/// 存储过程参数
/// SqlCommand对象
private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader Dr)
{
   SqlConnection Conn;
   Conn = new SqlConnection(ConnStr);
   Conn.Open();
   SqlCommand Cmd = new SqlCommand(procName, Conn);
   Cmd.CommandType = CommandType.StoredProcedure;
   if (prams != null)
   {
    foreach (SqlParameter parameter in prams)
     Cmd.Parameters.Add(parameter);
   }
   Cmd.Parameters.Add(
    new SqlParameter("ReturnValue", SqlDbType.Int, 4,
    ParameterDirection.ReturnValue, false, 0, 0,
    string.Empty, DataRowVersion.Default, null));

return Cmd;
}

///
/// 运行存储过程,返回.
///
/// 存储过程名
/// 存储过程参数
/// SqlDataReader对象
public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{

   SqlCommand Cmd = CreateCmd(procName, prams, Dr);
   Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   return;
}

///
/// 运行存储过程,返回.
///
/// 存储过程名
/// 存储过程参数
public string RunProc(string procName, SqlParameter[] prams)
{
   SqlDataReader Dr;
   SqlCommand Cmd = CreateCmd(procName, prams);
   Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
   if(Dr.Read())
   {
    return Dr.GetValue(0).ToString();
   }
   else
   {
    return "";
   }
}

///
/// 运行存储过程,返回dataset.
///
/// 存储过程名.
/// 存储过程入参数组.
/// dataset对象.
public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)
{
   SqlCommand Cmd = CreateCmd(procName,prams);
   SqlDataAdapter Da = new SqlDataAdapter(Cmd);
   try
   {
    Da.Fill(Ds);
   }
   catch(Exception Ex)
   {
    throw Ex;
   }
   return Ds;
}