全为静态方法,不用实例化直接使用,DataReader需要手动关闭(其Conn会自动关闭)
db.cs
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;
/// <summary>
/// db 的摘要说明
/// </summary>
public class db
... {
public db()
...{
}
//创建SqlConnection
public static SqlConnection con()
...{
SqlConnection con = new SqlConnection("server=.;database=vote;uid=sa;pwd=sa");
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["voteConn"].ConnectionString);
if (con.State.Equals(ConnectionState.Closed))
...{
con.Open();
}
return con;
}
//返回一个只读/向前的数据流
public static SqlDataReader getSdr(string qs)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
//返回一个数据集DataSet
public static DataSet getDs(string qs, string tableName)
...{
SqlConnection con = db.con();
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = new SqlCommand(qs, con);
DataSet ds = new DataSet();
sda.Fill(ds, tableName);
return ds;
}
//返回单条数据 String
public static string getCell(string qs)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
string cell = cmd.ExecuteScalar().ToString();
con.Close();
return cell;
}
//返回查询对象是否存在, 存在为True
public static bool exits(string qs)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
bool exits = (cmd.ExecuteScalar() != null);
con.Close();
return exits;
}
//执行一条Insert update delete语句, 并返回影响行数
public static int getExecuteCount(string qs)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
int count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
//执行多条语句,返回是否成功
public static bool executeTransaction(string[] qs)
...{
bool flag = true;
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
SqlTransaction myTransac = con.BeginTransaction();//声明一个连接初始化的SQL事物
try
...{
//for (int i = 0; i < qs.Length; i++)
//{
// cmd.Transaction = myTransac;//指定cmd的SQL事物
// cmd.CommandText = qs[i];
// cmd.ExecuteNonQuery();
//}
foreach (string query in qs)
...{
cmd.Transaction = myTransac;//指定cmd的SQL事物
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
...{
myTransac.Rollback();
flag = false;
}
finally
...{
myTransac.Commit();
con.Close();
}
return flag;
}
}