.NET 数据库连接及返回

.NET 数据库连接及返回DataSet、DataView的一个类

public SqlConnection conn;

//建立数据库连接
private void OpenConn()
{
conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Appbox;User ID=sa;Password=123456");
if (conn.State.ToString().ToLower() == "open")
{
//连接打开时
}
else
{
//连接关闭时
conn.Open();
}
}

//关闭数据库连接
private void CloseConn()
{
if (conn.State.ToString().ToLower() == "open")
{
//连接打开时
conn.Close();
conn.Dispose();
}
}

/// <summary>
/// 读取数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader DataRead(string sql)
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}

/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet DataSetReturn(string sql, string tableName)
{
OpenConn();
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter(sql, conn);
da.Fill(ds, tableName);
CloseConn();
return ds;
}

/// <summary>
/// 返回DataView
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataView DataViewReturn(string sql)
{
OpenConn();
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter(sql, conn);
da.Fill(ds, "temp");
CloseConn();
return ds.Tables[0].DefaultView;
}

/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable DataTableReturn(string sql)
{
OpenConn();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(dt);
CloseConn();
return dt;
}

/// <summary>
/// 执行一个SQL操作:添加、删除、更新操作
/// </summary>
/// <param name="sql"></param>
public void SqlServerExcute(str
ing sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
CloseConn();
}

/// <

.NET 数据库连接及返回DataSet、DataView的一个类

summary>
/// 执行一个SQL操作:添加、删除、更新操作，返回受影响的行
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int SqlServerExecuteNonQuery(string sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
int flag = cmd.ExecuteNonQuery();
return flag;
}

/// <summary>
/// 返回记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int SqlServerRecordCount(string sql)
{
OpenConn();
SqlCommand cmd = new SqlCommand();
http://doc.guandang.netmandText = sql;
cmd.Connection = conn;
SqlDataReader dr;
dr = cmd.ExecuteReader();
int RecordCount = -1;
while (dr.Read())
{
RecordCount = int.Parse(dr[0].ToString());
}
CloseConn();
return RecordCount;
}

/// <summary>
/// 过滤非法字符
/// </summary>
/// <param name="Str"></param>
/// <returns></returns>
public string FilterStr(string Str)
{
Str = Str.Trim();
Str = Str.Replace("*", "");
Str = Str.Replace("=", "");
Str = Str.Replace("/", "");
Str = Str.Replace("$", "");
Str = Str.Replace(&qu
ot;#", "");
Str = Str.Replace("@", "");
Str = Str.Replace("&", "");
return Str;
}