全为静态方法,不用实例化直接使用,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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/// <summary>
/// db 的摘要说明
/// </summary>
public
class
db
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
public db()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//创建SqlConnection
public static SqlConnection con()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
con.Open();
}
return con;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//返回一个只读/向前的数据流
public static SqlDataReader getSdr(string qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//返回一个数据集DataSet
public static DataSet getDs(string qs, string tableName)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = db.con();
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = new SqlCommand(qs, con);
DataSet ds = new DataSet();
sda.Fill(ds, tableName);
return ds;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//返回单条数据 String
public static string getCell(string qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
string cell = cmd.ExecuteScalar().ToString();
con.Close();
return cell;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//返回查询对象是否存在, 存在为True
public static bool exits(string qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
bool exits = (cmd.ExecuteScalar() != null);
con.Close();
return exits;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//执行一条Insert update delete语句, 并返回影响行数
public static int getExecuteCount(string qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand(qs, con);
int count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
//执行多条语句,返回是否成功
public static bool executeTransaction(string[] qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
bool flag = true;
SqlConnection con = db.con();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
SqlTransaction myTransac = con.BeginTransaction();//声明一个连接初始化的SQL事物
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//for (int i = 0; i < qs.Length; i++)
//{
// cmd.Transaction = myTransac;//指定cmd的SQL事物
// cmd.CommandText = qs[i];
// cmd.ExecuteNonQuery();
//}
foreach (string query in qs)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
cmd.Transaction = myTransac;//指定cmd的SQL事物
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
myTransac.Rollback();
flag = false;
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
myTransac.Commit();
con.Close();
}
return flag;
}
}