SqlData.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;
using System.Security.Cryptography;
using System.Text;
/// <summary>
/// SqlData 的摘要说明 注意给参数
/// </summary>
public class SqlData
{
#region 类中的全局变量
private SqlConnection sqlcon; //申明一个SqlConnection对象
private SqlCommand sqlcom; //申明一个SqlCommand对象
private SqlDataAdapter sqldata; //申明一个SqlDataAdapter对象
private string connectionstring;
#endregion
#region 构造函数
/// <summary>
/// 创建时间:2007－3－15
/// 创建人:朱江
/// 构造函数，初始化时连接数据库
/// </summary>
public SqlData(string Str)
{
sqlcon = new SqlConnection(ConfigurationManager.AppSettings[Str]);
sqlcon.Open(); //打开链接
connectionstring = Str;
}
#endregion
#region 关闭连接
/// <summary>
/// 创建时间:2007－3－15
/// 创建人:朱江
/// 构造函数，关闭连接
/// </summary>
public bool Close ()
{
try
{
sqlcon.Close();
return true;
}
catch
{
return false;
}
finally
{
}
}
#endregion
#region 更改数据库连接
/// <summary>
/// 创建时间:2008-7-23
/// 创建人:陆慧
/// 此方法返改变数据库连接
/// </summary>
/// <param name="Str">web.config中的连接字符串</param>
/// <returns></returns>
public void SqlCon(string Str)
{
sqlcon = new SqlConnection(ConfigurationManager.AppSettings[Str]);
sqlcon.Open(); //打开链接
connectionstring = Str;
}
#endregion
/// <summary>
/// 重新打开原来/前一次关闭的连接.
/// </summary>
public void SqlOpen()
{
sqlcon.Open(); //打开链接
}
#region 绑定用户页面中的GridView控件
/// <summary>
/// 创建时间:2007-3-15
/// 创建人:朱江
/// 此方法实现数据绑定到GridView中
/// </summary>
/// <param name="dl">要绑定的控件</param>
/// <param name="SqlCom">要执行的SQL语句</param>
/// <returns></returns>
public bool BindData(GridView dl, string SqlCom)
{
dl.DataSource = this.ExceDS(SqlCom);
try
{
dl.DataBind();
return true;
}
catch
{
return false;
}
finally
{
sqlcon.Close();
}
}
#endregion
#region 执行SQL语句
/// <summary>
/// 创建时间:2007-3-15
/// 创建人:朱江
/// 此方法用来执行SQL语句
/// </summary>
/// <param name="SqlCom">要执行的SQL语句</param>
/// <returns></returns>
public bool ExceSQL(string strSqlCom)
{
sqlcom = new SqlCommand(strSqlCom, sqlcon);
try
{
sqlcom.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
sqlcon.Close();
}
}
#endregion
//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)
#region 返回DataSet类型数据
/// <summary>
/// 创建时间:200-3-15
/// 创建人:朱江
/// 此方法返回一个DataSet类型
/// </summary>
/// <param name="SqlCom">要执行的SQL语句</param>
/// <returns></returns>
public DataSet ExceDS(string SqlCom)
{
try
{
sqlcom = new SqlCommand(SqlCom, sqlcon);
sqldata = new SqlDataAdapter();
sqldata.SelectCommand = sqlcom;
DataSet ds = new DataSet();
sqldata.Fill(ds);
return ds;
}
finally
{
sqlcon.Close();
}
}
#endregion
#region 返回DataSet类型数据
/// <summary>
/// 创建时间:2008-7-23
/// 创建人:陆慧
/// 执行不带参数的存贮过程
/// </summary>
/// <param name="SqlProc">要执行的存贮过程名字</param>
/// <returns></returns>
public bool ExceDS_Proc(string SqlProc)
{
try
{
sqlcom = new SqlCommand(SqlProc, sqlcon);
sqldata = new SqlDataAdapter();
sqldata.SelectCommand = sqlcom;
sqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
sqldata.Fill(ds);
return true;
}
catch
{
return false;
}
finally
{
sqlcon.Close();
}
}
#endregion
/// <summary>
/// 作者:陆慧
/// 执行带有参数的存贮过程.
/// 使用时外面的程序可以这么用
/// SqlParameter[] parameters = { new SqlParameter( "@id ",SqlDbType.Int), new SqlParameter( "@ParentId ",SqlDbType.Int) };
/// parameters[0].Value = 变量值;
/// parameters[1].Value = 变量值;
/// 记住,存贮过程中用什么变量名字,这里@id也改成相应的名字.
/// </summary>
/// <param name="SqlProc">存储过程名字</param>
/// <param name="prams">参数.</param>
/// <returns></returns>
public bool ExceDS_Proc_para(string SqlProc, SqlParameter[] prams)
{
/*
使用时外面的程序可以这么用
SqlParameter[] parameters = { new SqlParameter( "@id ",SqlDbType.Int), new SqlParameter( "@ParentId ",SqlDbType.Int) };
parameters[0].Value = 变量值;
parameters[1].Value = 变量值;
记住,存贮过程中用什么变量名字,这里@id也改成相应的名字.
*/
try
{
sqlcom = new SqlCommand(SqlProc, sqlcon);
sqldata = new SqlDataAdapter();
sqldata.SelectCommand = sqlcom;
sqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
sqlcom.Parameters.Add(parameter);
}
}
sqlcom.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
sqlcon.Close();
}
}
#region 返回SqlDataReader类型的数据
/// <summary>
/// 创建时间:2007-3-15
/// 创建人:朱江
/// 此方法返回一个SqlDataReader类型的参数
/// </summary>
/// <param name="SqlCom"></param>
/// <returns></returns>
public SqlDataReader ExceRead(string SqlCom)
{
sqlcom = new SqlCommand(SqlCom, sqlcon);
SqlDataReader read = sqlcom.ExecuteReader();
return read;
}
#endregion
/// <summary>
/// 创建时间:2009-3-18
/// 创建人:陆慧
/// 删除表,结束时关闭连接
/// </summary>
/// <param name="TableName">表名</param>
/// <returns></returns>
public bool DropTable(string TableName)
{
string drop = "drop table "+TableName+"";
bool b=ExceSQL(drop);
return b;
}
public bool ClearTable(string TableName)
{
string clear = "Delete from " + TableName + "";
bool b = ExceSQL(clear);
return b;
}
/// <summary>
/// 创建时间:2009-3-18
/// 创建人:陆慧
/// 设置表的主键
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="PrimaryKey">主键</param>
/// <returns></returns>
public bool TableSetPrimaryKey(string TableName, string PrimaryKey)
{
// sql例句ALTER TABLE temp ADD CONSTRAINT pk PRIMARY KEY (Nr)
string setprimarykey = " ALTER TABLE " + TableName + " ADD CONSTRAINT pk_" + TableName + " PRIMARY KEY (" + PrimaryKey + ")";
bool b= ExceSQL(setprimarykey);
return b;
}
/// <summary>
/// 设置标识
/// 注意!设置时,先会删除该字段再创建.
///
/// </summary>
/// <param name="TableName"></param>
/// <param name="PrimaryKey"></param>
/// <returns></returns>
public bool TableSetIdentity(string TableName, string PrimaryKey)
{
bool b = true;
try
{ //要设置标识,必须先删掉此字段,再添加.
string dropcolumn = "alter table " + TableName + " drop " + PrimaryKey + "";
ExceSQL(dropcolumn);
SqlOpen();
string setidentity = "alter table " + TableName + " add " + PrimaryKey + " int identiy(1,1)";
ExceSQL(setidentity);
}
catch
{
b = false;
}
return b;
}
/// <summary>
/// 创建时间:2009-3-18
/// 创建人:陆慧
/// 复制表结构和主键结构,若存在目标表名,先删除.
/// 注意,其他约束等无法复制
/// </summary>
/// <param name="SourceTableName">原表名</param>
/// <param name="DestTableName">目标表名</param>
/// <param name="PrimaryKey">主键字段名</param>
/// <returns></returns>
public bool DataTableCopy(string SourceTableName,string DestTableName,string PrimaryKey)
{
bool flag = true;
DropTable(DestTableName);
if (sqlcon.State == System.Data.ConnectionState.Closed)
{
SqlOpen();
}
try
{
string sel = "select * into " + DestTableName + " from " + SourceTableName + "";
ExceSQL(sel);
SqlOpen();
string del = "delete from " + DestTableName + "";
ExceSQL(del);
SqlOpen();
TableSetIdentity(DestTableName, PrimaryKey);
SqlOpen();
TableSetPrimaryKey(DestTableName, PrimaryKey);
}
catch
{
flag = false;
}
finally
{
if (sqlcon.State == System.Data.ConnectionState.Open)
{
sqlcon.Close();
}
}
return flag;
}
/// <summary>
/// 删除以startname开头的表
/// </summary>
/// <param name="startname"></param>
/// <returns></returns>
public bool DeleteTableWithStartName(string startname)
{
bool b = true;
string select = "select 'drop table '+name as SQL from sysobjects where xtype='u' and name like '" + startname + "%'";
SqlDataReader dr = ExceRead(select);
try
{
while (dr.Read())
{
SqlData da = new SqlData(connectionstring);
da.ExceSQL(dr["SQL"].ToString());
}
}
catch
{
b = false;
}
dr.Close();
sqlcon.Close();
return b;
}
public string Encrypt(string strInput)
{
//转换为UTF8编码
byte[] b = Encoding.UTF8.GetBytes(strInput);
//计算字符串UTF8编码后的的MD5哈希值，并转换为字符串
MD5 md5 = new MD5CryptoServiceProvider();
return Encoding.UTF8.GetString(md5.ComputeHash(b));
}
public static string MD5(string str, int code)
{
if (code == 16) //16位MD5加密（取32位加密的9~25字符）
{
return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "MD5").ToLower().Substring(8, 16);
}
else//32位加密
{
return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "MD5").ToLower();
}
}
//
public class MyTemplate : ITemplate
{
private string colname;
public MyTemplate(string colname)
{
this.colname = colname;
}
public void InstantiateIn(Control container)
{
LiteralControl l = new LiteralControl();
l.DataBinding += new EventHandler(this.OnDataBinding);
container.Controls.Add(l);
}
public void OnDataBinding(object sender, EventArgs e)
{
LiteralControl l = (LiteralControl)sender;
GridViewRow container = (GridViewRow)l.NamingContainer;
l.Text = ((DataRowView)container.DataItem)[colname].ToString();
}
}
}
