数据库操作有很多繁琐的细节,在这儿我把
它们封装在一起,形成一个DBManager,再把它做成一个组件,今后用起来就特别方便了。欢迎指教。
//作者:v1.2.0.2
/创建/时间:2007.11.17
//作者:参天树
/创建/时间:2007.11.17
//作者:参天树
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DB
{
public class DBManager
{
private static SqlConnection con = null;
private static SqlCommand cmd = new SqlCommand();
//打开并返回一个连接
public static SqlConnection GetCon()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
con.Open();
return con;
}
{
public class DBManager
{
private static SqlConnection con = null;
private static SqlCommand cmd = new SqlCommand();
//打开并返回一个连接
public static SqlConnection GetCon()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
con.Open();
return con;
}
public static void Clear()
{
if (con.State == ConnectionState.Open)
con.Close();
}
{
if (con.State == ConnectionState.Open)
con.Close();
}
//1.执行sql语句,不返回结果(可用于添加,删除操作,)
public static void Exec(string strSql)
{
cmd.Connection = GetCon();
cmd.CommandText = strSql;
int count = cmd.ExecuteNonQuery();
Clear();
}
public static void Exec(string strSql)
{
cmd.Connection = GetCon();
cmd.CommandText = strSql;
int count = cmd.ExecuteNonQuery();
Clear();
}
//2.根据传入的sql语句,执行并返回相关记录的条数(一般是应用于用户登陆的身份验证)
public static int ExecAndGetCount(string strSql)//strSql 是传入的sql命令语句
{
//得到数据库的连接,并且这个连接是打开的
public static int ExecAndGetCount(string strSql)//strSql 是传入的sql命令语句
{
//得到数据库的连接,并且这个连接是打开的
//建立一个命令对象
cmd.Connection = GetCon();
cmd.CommandText = strSql;
cmd.Connection = GetCon();
cmd.CommandText = strSql;
string result = cmd.ExecuteScalar().ToString();//执行查询,并返回结果的第一行的第一列
int count = Convert.ToInt32(result);
Clear();
int count = Convert.ToInt32(result);
Clear();
return count;
}
}
//3.根据传入的sql语句,执行并返回DataReader对象
public static SqlDataReader ExecAndGetSdr(string strSql)
{
cmd.Connection = GetCon();
cmd.CommandText = strSql;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
public static SqlDataReader ExecAndGetSdr(string strSql)
{
cmd.Connection = GetCon();
cmd.CommandText = strSql;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
//4.根据传入的sql语句,执行并返回DataReader对象
public static DataSet ExecAndGetDs(string strSql, string tableName)
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, GetCon());
DataSet ds = new DataSet();
sda.Fill(ds, tableName);
Clear();
return ds;
}
//5.定义一个应用事务机制批量处理多条sql语句的方法
public static bool ExecInTransation(string[] strSql)
{
bool flag = false;
con = GetCon();
cmd.Connection = con;
SqlTransaction myTransation;
myTransation = con.BeginTransaction();
try
{
for (int i = 0; i < strSql.Length; i++)
{
cmd.Transaction = myTransation;
cmd.CommandText = strSql[i];
cmd.ExecuteNonQuery();
}
public static DataSet ExecAndGetDs(string strSql, string tableName)
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, GetCon());
DataSet ds = new DataSet();
sda.Fill(ds, tableName);
Clear();
return ds;
}
//5.定义一个应用事务机制批量处理多条sql语句的方法
public static bool ExecInTransation(string[] strSql)
{
bool flag = false;
con = GetCon();
cmd.Connection = con;
SqlTransaction myTransation;
myTransation = con.BeginTransaction();
try
{
for (int i = 0; i < strSql.Length; i++)
{
cmd.Transaction = myTransation;
cmd.CommandText = strSql[i];
cmd.ExecuteNonQuery();
}
myTransation.Commit();
flag = true;
}
catch (Exception e)
{
myTransation.Rollback();
flag = false;
}
finally
{
Clear();
}
flag = true;
}
catch (Exception e)
{
myTransation.Rollback();
flag = false;
}
finally
{
Clear();
}
return flag;
}
}
}
}
转载于:https://blog.51cto.com/119244/50885