📄 database.cs
字号:
//--------------------------------------------------------------------------------------------------------------------------
//项目名称: 综合缴费平台
//模块名称: 数据库访问
//建立人员: 张春阳
//建立日期: 20080808
//编程语言: C#
//模块说明: 数据库访问通用类
//其 它:
//--------------------------------------------------------------------------------------------------------------------------
//修改人员:
//修改日期:
//修改说明:
//其 它:
//==========================================================================================================================
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Text;
using System.Runtime.InteropServices;
#region 数据访问实用方法
/// <summary>
/// 通用数据库类
/// </summary>
///
/// <summary>
/// 使用webconfig中的连接字符串
/// </summary>
public class DataBase
{
private static string ConnStr = ConfigurationManager.ConnectionStrings["connstra"].ConnectionString;
public DataBase()
{
//ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
}
/// <summary>
/// 自定义连接数据库
/// </summary>
/// <param name="Str">连接字符串</param>
// public DataBase(string Str)
// {
//// try
//// {
//// this.ConnStr = Str;
////
//// }
//// catch(Exception ex)
//// {
//// throw ex;
//// }
// }
/// <summary>
/// 使用配置文件读取数据库连接字符串
/// </summary>
/// <param name="fileName">配置文件路径</param>
/// <param name="strini">配置文件类型 ini,pg</param>
// public DataBase(string strfileName,string strini)
// {
// try
// {
// Para par = new Para(strfileName);
// string strConText = par.GetIni();
// if(strConText != "")
// {
// this.ConnStr = strConText;
// }
//
// }
// catch(Exception ex)
// {
// throw ex;
// }
// }
/// <summary>
/// 使用配置文件读取数据库连接字符串,自定义数据库名称
/// </summary>
/// <param name="fileName">配置文件路径</param>
/// <param name="strini">配置文件类型 ini,pg</param>
/// <param name="strDataName">数据库名称</param>
// public DataBase(string strfileName,string strini,string strDataName)
// {
// try
// {
// Para par = new Para(strfileName);
//
// string strConText = par.GetIni(strDataName);
// if(strConText != "")
// {
// this.ConnStr = strConText;
// }
//
// }
// catch(Exception ex)
// {
// throw ex;
// }
// }
/// <summary>
/// 返回connection对象
/// </summary>
/// <returns></returns>
private static SqlConnection ReturnConn()
{ SqlConnection Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
}
catch(Exception ev)
{
Logs.Write(ev);
}return Conn;
}
public static void Dispose(SqlConnection Conn)
{
try
{
if(Conn!=null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
catch(Exception ev)
{
Logs.Write(ev);
}
}
/// <summary>
/// 运行SQL语句返回DataReader
/// </summary>
/// <param name="SQL"></param>
/// <returns>SqlDataReader对象.</returns>
public static SqlDataReader RunProcGetReader(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
SqlDataReader Dr = null;
try
{
Conn.Open();
SqlCommand Cmd ;
Cmd = CreateCmd(SQL, Conn);
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Dr;
//Dispose(Conn);
}
/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
private static SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
SqlCommand Cmd = null;
try
{
Cmd = new SqlCommand(SQL, Conn);
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Cmd;
}
/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
private static SqlCommand CreateCmd(string SQL)
{
SqlConnection Conn = null;
Conn = new SqlConnection(ConnStr);
SqlCommand Cmd = null ;
try
{
Conn.Open();
Cmd = new SqlCommand(SQL, Conn);
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Cmd;
}
/// <summary>
/// 返回adapter对象
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public static SqlDataAdapter CreateDa(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
SqlDataAdapter Da = null;
try
{
Conn.Open();
Da = new SqlDataAdapter(SQL, Conn);
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Da;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
public static DataSet RunProc(string SQL ,DataSet Ds)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
SqlDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new SqlDataAdapter(SQL,Conn);
Da.Fill(Ds);
}
catch(Exception Err)
{
Logs.Write(Err);
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="SQL">SQL语句</param>
public static DataSet RunProc(string SQL)
{ SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
DataSet Ds = new DataSet();
try
{
Conn.Open();
SqlDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new SqlDataAdapter(SQL,Conn);
Da.Fill(Ds);
}
catch(Exception Err)
{
Logs.Write(Err);
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public static DataSet RunProc(string SQL ,DataSet Ds,string tablename)
{ SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
SqlDataAdapter Da;
Da = CreateDa(SQL);
Da.Fill(Ds,tablename);
}
catch(Exception Ex)
{
Logs.Write(Ex);
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public static DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename )
{ SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
SqlDataAdapter Da ;
Da = CreateDa(SQL);
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch(Exception Ex)
{
Logs.Write(Ex);
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 检验是否存在数据
/// </summary>
/// <returns></returns>
public static bool ExistDate(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
SqlDataReader Dr = null;
try
{
Conn.Open();
Dr = CreateCmd(SQL,Conn).ExecuteReader();
}
catch(Exception ev)
{
Logs.Write(ev);
}
if (Dr.Read())
{
Dispose(Conn);
return true;
}
else
{
Dispose(Conn);
return false;
}
}
/// <summary>
/// 返回SQL语句执行结果的第一行第一列
/// </summary>
/// <returns>字符串</returns>
public static string ReturnValue(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
string result;
SqlDataReader Dr = null;
try
{Conn.Open();
Dr = CreateCmd(SQL,Conn).ExecuteReader();
}
catch(Exception ev)
{
Logs.Write(ev);
}
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
Dispose(Conn);
return result;
}
/// <summary>
/// 返回SQL语句第一列,第ColumnI列,
/// </summary>
/// <returns>字符串</returns>
public static string ReturnValue(string SQL, int ColumnI)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
string result;
SqlDataReader Dr = null;
try
{
Conn.Open();
Dr = CreateCmd(SQL,Conn).ExecuteReader();
}
catch(Exception ev)
{
Logs.Write(ev);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = "";
}
Dr.Close();
Dispose(Conn);
return result;
}
/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>sqlcommand对象.</returns>
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
try
{
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if(parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Cmd;
}
/// <summary>
/// 为存储过程生成一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <returns>SqlCommand对象</returns>
private static 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;
try
{
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));
}
catch(Exception ev)
{
Logs.Write(ev);
}
return Cmd;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <param name="dataReader">SqlDataReader对象</param>
public static void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
try
{
SqlCommand Cmd = CreateCmd(procName, prams, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch(Exception ev)
{
Logs.Write(ev);
}
return;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
public static string RunProc(string procName, SqlParameter[] prams)
{
SqlDataReader Dr = null;
try
{
SqlCommand Cmd = CreateCmd(procName, prams);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch(Exception ev)
{
Logs.Write(ev);
}
if(Dr.Read())
{
return Dr.GetValue(0).ToString();
}
else
{
return "";
}
}
/// <summary>
/// 运行存储过程,返回dataset.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>dataset对象.</returns>
public static 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)
{
Logs.Write(Ex);
}
return Ds;
}
/// <summary>
/// 多条SQL语句查询 事务处理
/// </summary>
/// <param name="sql">SQL语句数组</param>
/// <returns>事务执行是否成功</returns>
public static bool ExecSqlCommand(string[] sql)
{bool result = true;
SqlConnection conn = new SqlConnection(ConnStr);
try
{
conn.Open();
SqlTransaction myTran = conn.BeginTransaction();
SqlCommand[] sqlCom = new SqlCommand[sql.Length];
for (int i = 0; i < sql.Length;i++ )
{
sqlCom[i] = new SqlCommand(sql[i], conn);
sqlCom[i].Transaction = myTran;
}
for (int i = 0; i < sql.Length; i++)
sqlCom[i].ExecuteNonQuery();
myTran.Commit();
}
catch (Exception ev)
{
Logs.Write(ev);
}
finally
{
conn.Close();
}
return result;
}
/// <summary>
/// 运行SQL语句
/// </summary>
/// <param name="SQL">SQL语句</param>
public static void RunSQL(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
Cmd.ExecuteNonQuery();
}
catch(Exception ev)
{
Logs.Write(ev);
}
Dispose(Conn);
return;
}
/// <summary>
/// 返回ＳＱＬ语句影响的行数
/// </summary>
/// <param name="SQL">ＳＱＬ语句</param>
/// <returns>ＳＱＬ语句影响的行数</returns>
public static int CommNum(string SQL)
{
int commnum = 0;
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
commnum = Cmd.ExecuteNonQuery();
}
catch(Exception ev)
{
Logs.Write(ev);
}
Dispose(Conn);
return commnum;
}
}
#endregion
// public class Global
// {
// public static Log log = new Log();
// }
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -