我的数据访问类库
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using OWC;
using System.IO;
using System.Drawing.Imaging;
using System.Security;
using System.Security.Cryptography;
using System.Text;
namespace Business
{
#region 向DataGrid控件动态绑定模板列专用类ColumnTemplate
//自定义类，用于向DataGrid控件动态绑定模板列专用
public class ColumnTemplate : ITemplate
{
private string column;
int Kind;
//private bool validate;
public ColumnTemplate(string column,int Kind)
{
this.column = column;
this.Kind = Kind;
}
public void InstantiateIn(Control container)
{
if(Kind == 0)//代表添加复选框
{
CheckBox mycheckbox = new CheckBox();
mycheckbox.ID = "Check"+column;
mycheckbox.DataBinding += new EventHandler(this.BindData);
container.Controls.Add(mycheckbox);
}
if(Kind == 1)//代表添加文本框
{
Label myLabel = new Label();
myLabel.ID = "myLabel"+column;
myLabel.DataBinding += new EventHandler(this.BindData);
container.Controls.Add(myLabel);
}
}
public void BindData(object sender, EventArgs e)
{
if(Kind == 0)
{
CheckBox mycheckbox = (CheckBox)sender;
DataGridItem container = (DataGridItem) mycheckbox.NamingContainer;
//mycheckbox.Checked = bool.Parse(((DataRowView) container.DataItem)[column].ToString());
object o = DataBinder.Eval(container.DataItem,column);
mycheckbox.Checked = bool.Parse(o.ToString());
}
if(Kind == 1)
{
Label myLabel = (Label)sender;
DataGridItem container = (DataGridItem) myLabel.NamingContainer;
object o = DataBinder.Eval(container.DataItem,column);
if(bool.Parse(o.ToString()))
myLabel.Text = "男";
else
myLabel.Text = "女";
}
}
}
#endregion
#region 常用方法专用类Method
//常用方法
public class MyMethod
{
#region 构造函数
public MyMethod()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#endregion
#region 组合信息框添充FillUnit
/*
* 功能:添充组合框控件
* 参数
* ----------------
* UnitID: 传入要添充的组合框控件ID
* All: 组合框第一项是否显示全部
* page: 宿主服务器请求页面
* TableName: 要操作的表名称
* FieldName: 要读取的字段名称,即下拉控件中显示的名称
* ----------------
*/
public static bool FillUnit(DropDownList UnitID,bool All,string TableName,string FieldName,Page page)
{
System.Web.UI.WebControls.ListItem ListItem;
string Query = "select * from "+TableName;
SqlCommand l_cmd;
SqlDataReader l_Reader;
//获取数据库连接字符串
String strConn = GetSqlConn(page);
SqlConnection l_conn = new SqlConnection(strConn);
try
{
l_conn.Open();
l_cmd = new SqlCommand(Query,l_conn);
l_Reader = l_cmd.ExecuteReader();
ListItem = new System.Web.UI.WebControls.ListItem();
if(All)
{
ListItem.Value ="0";
ListItem.Text = "全部";
UnitID.Items.Add(ListItem);
}
while (l_Reader.Read())
{
ListItem = new System.Web.UI.WebControls.ListItem();
ListItem.Value = l_Reader["ID"].ToString();
ListItem.Text = l_Reader[FieldName].ToString();
UnitID.Items.Add(ListItem);
}
l_Reader.Close();
l_conn.Close();
l_conn.Dispose();
l_cmd.Dispose();
return true;
}
catch(Exception e)
{
l_conn.Close();
Message(e.ToString(),page);
return false;
}
finally
{
l_conn.Close();
}
}
#endregion
#region 获取数据库连接字符串GetSqlConn
/*
* 功能:获取数据库连接字符串
* 参数
* ----------------
* page: 宿主服务器请求页面
* ----------------
*/
public static String GetSqlConn(Page page)
{
String Str="";
if(HttpContext.Current.Application["SqlConn"]==null)
Str = "";
else
Str = HttpContext.Current.Application["SqlConn"].ToString();
if(Str == "")
{
Str = System.Configuration.ConfigurationSettings.AppSettings["connString"];
if(Str == null)
Str = "";
HttpContext.Current.Application["Sqlconn"]=Str;
}
if(Str == "")
Message("连接字符串获取失败,请联系系统管理员!",page);
return Str;
}
#endregion
#region 导出Excel函数ImportExcel
/*
* 功能:从数据库表中导出Excel
* 参数
* ----------------
* QueryStr: 要导出Excel表的SQL语句
* TableName: 主要用来保存导出的Excel的表名
* page: 宿主服务器请求页面
* ----------------
*/
public static void ImportExcel(string QueryStr,string TableName,Page page)
{
string strConn = GetSqlConn(page);
SqlConnection conn = new SqlConnection(strConn);
DataSet dss= new DataSet();
try
{
//随便输入SQL语句
SqlDataAdapter adapter= new SqlDataAdapter(QueryStr,conn);
adapter.Fill(dss,"Customer");
string sFileName = TableName+DateTime.Now.ToFileTime().ToString() + ".xls";
OWC.SpreadsheetClass xlsheet = new OWC.SpreadsheetClass();
DataTable dt = dss.Tables[0];
int numbercols = dt.Columns.Count;
//插入列名
for (int i = 0 ; i < numbercols ; i++)
{
xlsheet.ActiveSheet.Cells[1,i+1] = dt.Columns[i].ColumnName;
//xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
//xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";
//xlsheet.get_Range(xlsheet.Cells[1,i+1],xlsheet.Cells[1,i+1]).Borders.LineStyle=1;
}
//xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
//xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";
//插入数据
for (int k = 0 ; k < dt.Rows.Count ; k++)
for (int i=0;i<numbercols;i++)
xlsheet.ActiveSheet.Cells[k+2,i+1] = dt.Rows[k][i].ToString();
//保存为本地临时文件，用户下载完后删除
DirectoryInfo di = null;
try
{
di = new DirectoryInfo("c:\\ExportExcel");
if(!di.Exists)
{
di.Create();
}
}
catch
{
conn.Close();
conn.Dispose();
dss.Dispose();
return;
}
xlsheet.ActiveSheet.Export("c:\\ExportExcel\\" + sFileName,OWC.SheetExportActionEnum.ssExportActionNone);
DownloadFiles("c:\\ExportExcel\\" + sFileName,page);
RemoveFiles("c:\\ExportExcel\\" + sFileName);
}
catch(Exception e)
{
conn.Close();
conn.Dispose();
dss.Dispose();
Message(e.ToString(),page);
return;
}
finally
{
conn.Close();
conn.Dispose();
}
}
/*
private void ExportExcel(DataGrid dgBrowse,System.Web.HttpResponse Response)
{
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
//输出html格式的东西
oHtmlTextWriter.Write("<center><b><font size=4>Suzhou Singapore International School</font></b><br/>");
oHtmlTextWriter.Write("<font size=3>xx</font></center>");
//把dgBrowse换成你的datagrid的id
this.dgBrowse.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
*/
#endregion
#region 下载文件DownloadFiles
/*
* 功能:从服务器下载文件至本地
* 参数
* ----------------
* strPath: 要保存至本地的路径
* page: 宿主服务器请求页面
* ----------------
*/
private static void DownloadFiles(string strPath,Page page)
{
//HttpContext.Current.Response
try
{
FileInfo fi=new FileInfo(strPath);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = false;
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(fi.FullName,System.Text.Encoding.UTF8));
HttpContext.Current.Response.AppendHeader("Content-Length",fi.Length.ToString());
HttpContext.Current.Response.WriteFile(fi.FullName);
HttpContext.Current.Response.Flush();
}
catch(Exception e)
{
HttpContext.Current.Response.End();
Message(e.ToString(),page);
}
}
#endregion
#region 删除文件RemoveFiles
/*
* 功能:删除文件
* 参数
* ----------------
* strPath: 文件路径
* ----------------
*/
private static void RemoveFiles(string strPath)
{
FileInfo fi = new FileInfo(strPath);
fi.Delete();
HttpContext.Current.Response.End();
}
#endregion
#region 从数据库中下载文件DownloadDBFiles
/*
* 功能:删除文件
* 参数
* ----------------
* sql: 传过来的SQL语句
* page: 宿主服务器请求页面
* FileLegth: 要下载的文件长度
* FileName: 要保存的的文件名
* FielField: 存文件的字段名
* ----------------
*/
public void DownloadDBFiles(string sql,Page page,string FileLegth,string FileName,string FileField)
{
string length,Name;
string strConn = GetSqlConn(page);
SqlConnection Connection = new SqlConnection(strConn);
SqlCommand command = new SqlCommand(sql,Connection);
try
{
Connection.Open();
SqlDataReader dr = command.ExecuteReader();
if(dr.Read())
{
page.Response.Clear();
//length = dr["文件长度"].ToString();
length = dr[FileLegth].ToString();
//Name = dr["文件名"].ToString();
Name = dr[FileName].ToString();
page.Response.Clear();
page.Response.ClearHeaders();
page.Response.Buffer = false;
page.Response.ContentType = "application/octet-stream";
page.Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(Name,System.Text.Encoding.UTF8));
page.Response.AppendHeader("Content-Length",length);
//Response.BinaryWrite((byte[])dr["文件"]);
page.Response.BinaryWrite((byte[])dr[FileField]);
page.Response.Flush();
}
dr.Close();
Connection.Close();
Connection.Dispose();
command.Dispose();
}
catch(Exception e)
{
Connection.Close();
Connection.Dispose();
command.Dispose();
Message(e.ToString(),page);
return;
}
finally
{
Connection.Close();
Connection.Dispose();
command.Dispose();
}
}
#endregion
#region 判断传过来的日期是否附和间隔要求
/*
* 功能:判断传过来的日期是否附和间隔要求
* 参数
* ----------------
* CheckDate: 要检察的日期
* DayNum: 间间隔天数
* ----------------
*/
public bool DateCheck(string CheckDate,string DayNum)
{
System.DateTime Date = DateTime.Parse(CheckDate);
int Num;
System.DateTime Current;
Current = DateTime.Today;
System.TimeSpan tSpan = Current-Date;
Num = tSpan.Days;
if(Num>=int.Parse(DayNum))
return false;
else
return true;
}
#endregion
#region 判断表中是否用到某字段,如用到返回False,否则为True,为了用来检测记录是否可被删除JudgeDel
/*
* 功能:判断某表中某字段是否有某值
* 参数
* ----------------
* TableName: 要检察的表名
* FieldName: 字段名
* FieldValue: 字段值
* ----------------
*/
public bool JudgeDel(string TableName,string FieldName,string FieldValue,Page page)
{
string sql = "select ID from "+TableName+" where "+FieldName+"="+FieldValue;
bool ReturnValue = false;
string strConn = GetSqlConn(page);
SqlConnection Connection = new SqlConnection(strConn);
SqlCommand command = new SqlCommand(sql,Connection);
SqlDataReader dr = null;
try
{
Connection.Open();
dr = command.ExecuteReader();
if(dr.Read())
ReturnValue = false;
else
ReturnValue = true;
dr.Close();
Connection.Close();
Connection.Dispose();
command.Dispose();
return ReturnValue;
}
catch(Exception e)
{
dr.Close();
Message(e.ToString(),page);
return false;
}
finally
{
Connection.Close();
Connection.Dispose();
command.Dispose();
}
}
#endregion
#region 加密Encrypt
/*
* 功能:加密字符串
* 参数
* ----------------
* strText: 要加密的字符串
* ----------------
*/
public static string Encrypt(string strText)
{
//string strEncrKey="code's key"; OLEUsuallyMethod.Encrypt( OLEUsuallyMethod.Decrypt
string strEncrKey="zsgg11111";
Byte[] byKey = {};
Byte[] IV = {0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF};
try
{
byKey = System.Text.Encoding.UTF8.GetBytes(strEncrKey.Substring(0, 8));
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
Byte[] inputByteArray = System.Text.Encoding.UTF8.GetBytes(strText);
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(byKey, IV), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
return Convert.ToBase64String(ms.ToArray());
}
catch(Exception ex)
{
return ex.Message;
}
}
#endregion
#region 解密Decrypt
/*
* 功能:解密字符串
* 参数
* ----------------
* strText: 要解密的字符串
* ----------------
*/
public static string Decrypt(string strText)
{
//string strDecrKey="code's key";
string strDecrKey="zsgg11111";
Byte[] byKey = {};
Byte[] IV = {0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF};
Byte[] inputByteArray = new byte[strText.Length];
try
{
byKey = System.Text.Encoding.UTF8.GetBytes(strDecrKey.Substring(0, 8));
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
inputByteArray = Convert.FromBase64String(strText);
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
return encoding.GetString(ms.ToArray());
}
catch(Exception ex)
{
return ex.Message;
}
}
#endregion
#region 创建缩略图CreateThumbnail
public Bitmap CreateThumbnail(string lcFilename,int lnWidth, int lnHeight)
{
System.Drawing.Bitmap bmpOut = null;
try
{
Bitmap loBMP = new Bitmap(lcFilename);
ImageFormat loFormat = loBMP.RawFormat;
decimal lnRatio;
int lnNewWidth = 0;
int lnNewHeight = 0;
//*** If the image is smaller than a thumbnail just return it
if (loBMP.Width < lnWidth && loBMP.Height < lnHeight)
return loBMP;
if (loBMP.Width > loBMP.Height)
{
lnRatio = (decimal) lnWidth / loBMP.Width;
lnNewWidth = lnWidth;
decimal lnTemp = loBMP.Height * lnRatio;
lnNewHeight = (int)lnTemp;
}
else
{
lnRatio = (decimal) lnHeight / loBMP.Height;
lnNewHeight = lnHeight;
decimal lnTemp = loBMP.Width * lnRatio;
lnNewWidth = (int) lnTemp;
}
// System.Drawing.Image imgOut =
// loBMP.GetThumbnailImage(lnNewWidth,lnNewHeight,
// null,IntPtr.Zero);
// *** This code creates cleaner (though bigger) thumbnails and properly
// *** and handles GIF files better by generating a white background for
// *** transparent images (as opposed to black)
bmpOut = new Bitmap(lnNewWidth, lnNewHeight);
Graphics g = Graphics.FromImage(bmpOut);
g.FillRectangle( Brushes.White,0,0,lnNewWidth,lnNewHeight);
g.DrawImage(loBMP,0,0,lnNewWidth,lnNewHeight);
loBMP.Dispose();
}
catch
{
return null;
}
return bmpOut;
}
#endregion
#region 提示窗口Message
/*
* 功能:弹出提示窗体
* 参数
* ----------------
* strText: 要加密的字符串
* page: 宿主服务器请求页面
* ----------------
*/
public static void Message(string str_Message,Page page)
{
page.RegisterStartupScript("","<script>alert('"+str_Message+"');</script>");
}
#endregion
#region 判断是否为数字IsNum
/*
* 功能:判断用户输入是不是数字
* 参数
* ----------------
* oText: 要判断的字符串
* ----------------
*/
public bool IsNum(string oText)
{
try
{
int var1=Convert.ToInt32 (oText);
return true;
}
catch
{
return false;
}
}
#endregion
#region 判断是否为日期IsDate
/*
* 功能:判断用户输入是不是日期类型
* 参数
* ----------------
* oText: 要判断的字符串
* ----------------
*/
public bool IsDate(string oText)
{
try
{
DateTime var1=Convert.ToDateTime(oText);
return true;
}
catch
{
return false;
}
}
#endregion
}
#endregion
#region 缩略图用相关类npdata
public class npdata
{
#region 构造函数
public npdata()
{
//
// TODO: Add constructor logic here
//
}
#endregion
// Returns a DataTable with the set of data in the albums table
public static DataTable GetAlbums()
{
// Connect
DataTable dt = new DataTable();
/*using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnStr"]))
{
// Just have the adapter fill the table
SqlDataAdapter adap = new SqlDataAdapter("SELECT *,"+
"(SELECT COUNT(*) FROM pics WHERE pics.albumid=albums.id) AS piccount "+
"FROM albums", conn);
conn.Open();
adap.Fill(dt);
conn.Close();
}
*/
return dt;
}
// Returns a DataTable with the set of data in the albums table
public static DataTable GetPicturesInAlbum(int id,String strConn)
{
// Connect
DataTable dt = new DataTable();
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * from Products where Auditing=-1", conn);
adap.Fill(dt);
}
catch(Exception e2)
{
conn.Close();
}
/*
using (SqlConnection conn = new SqlConnection(strConn))
{
// The adapter to select pictures from a given album
conn.Open();
SqlDataAdapter adap = new SqlDataAdapter("SELECT * from Products", conn);
//adap.SelectCommand.Parameters.Add("@albumid", SqlDbType.Int, 0).Value = id;
adap.Fill(dt);
}
// Done
*/
conn.Close();
return dt;
}
// Given an pic ID, returns the path on the server filesystem to that picture
// and also the number of views for that picture
public static string GetPathToPicture(int picID, out int numViews,HttpContext Context,string Kind)
{
string Kind2=Context.Request["Kind"].ToString();
/*
OleDbCommand myCmd = new OleDbCommand(Str,myConn);
myConn.Open();
OleDbDataReader myDr;
myDr=myCmd.ExecuteReader();
myDr.Read();
FreeTextBox1.Text = myDr["新闻内容"].ToString();
*/
// Set the command parameter and execute the reader
numViews = 0;
string FileName = "";
//String strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\\wwwroot\\LDRSite\\lvdel.mdb;Persist Security Info=False;Jet OLEDB:Database Password=lvdelgl";//Context.Session["ConnStr"].ToString();
/*
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
strConn += Context.Server.MapPath("\\International\\database\\tiyisw.mdb");
strConn += ";Persist Security Info=False;Jet OLEDB:Database Password=";
strConn += System.Configuration.ConfigurationSettings.AppSettings["Password"];
*/
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
strConn += Context.Request.PhysicalApplicationPath;
strConn += "\\database\\tiyisw.asp";
strConn += ";Persist Security Info=False;Jet OLEDB:Database Password=";
//String strConn = Context .Session["ConnStr"].ToString();
OleDbConnection Connection = new OleDbConnection(strConn);
string StrPath=Context.Request.PhysicalApplicationPath+"\\Img\\";//Context.Server.MapPath("");
string QueryStr="";
if(Kind2=="0")
QueryStr = "select PicFileName from InternationalNews where ID="+picID;//"select StoreFileName,OriginaFileName,DataClass.Path,numviews from Data,DataClass where Data.Class = DataClass.ID and Data.ID ="+picID;
if(Kind2 == "1")
QueryStr = "select FileName from PicListInfo where ID="+picID;
OleDbCommand cmd2 = new OleDbCommand(QueryStr,Connection);
Connection.Open();
OleDbDataReader Reader2 = cmd2.ExecuteReader();
if (Reader2.Read())
{
//StrPath = Reader2["Path"].ToString();
numViews = 0;//(int)Reader2["numviews"];
FileName = Reader2["PicFileName"].ToString();
}
Reader2.Close();
Connection.Close();
StrPath = StrPath;
StrPath += "\\"+FileName;
return StrPath;
/*
string imgpath = "";
using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]))
{
SqlCommand cmd = new SqlCommand("SELECT OriginaFileName as filename,numviews,albums.rootpath FROM pics " +
"INNER JOIN albums ON albums.id=pics.albumid " +
"WHERE pics.id=@picid", conn);
cmd.Parameters.Add("@picid", SqlDbType.Int).Value = picID;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
// Construct the image path using the album root folder and the image filename
imgpath = reader["rootpath"] + @"\" + reader["filename"];
numViews = (int)reader["numviews"];
}
reader.Close();
conn.Close();
}
return imgpath;
*/
}
// Given an pic ID sets the number of views for that picture
public static void SetNumViews(int picid, int numViews)
{
/*
using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnStr"]))
{
// The command to increment the number of views a picture has
SqlCommand cmd = new SqlCommand("UPDATE pics SET numviews=@numviews WHERE id=@picid", conn);
cmd.Parameters.Add("@numviews", SqlDbType.Int).Value = numViews;
cmd.Parameters.Add("@picid", SqlDbType.Int).Value = picid;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
*/
}
// Given an pic ID, returns the title and description for that picture. If the
// title is NULL, returns the filename as the title.
/*
public static void GetPicInfo(int picid, out string title, out string description)
{
title = "";
description = "";
using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnStr"]))
{
// The command to select a specific picture data
SqlCommand cmd = new SqlCommand("SELECT ISNULL(title, filename) AS returntitle, ISNULL([description],'') AS returndesc FROM pics WHERE pics.id=@picid", conn);
cmd.Parameters.Add("@picid", SqlDbType.Int, 0).Value = picid;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
// Read returned values
title = (string)reader["returntitle"];
description = (string)reader["returndesc"];
}
reader.Close();
conn.Close();
}
}
*/
// Given an pic ID, sets the title and description for that picture
public static void UpdatePicInfo(int picid, string title, string description)
{
/*
using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnStr"]))
{
// The command to update picture data
SqlCommand cmd = new SqlCommand("UPDATE pics SET title=@title,description=@description WHERE pics.id=@picid", conn);
cmd.Parameters.Add("@title", SqlDbType.VarChar, 255).Value = title;
cmd.Parameters.Add("@description", SqlDbType.VarChar, 4096).Value = description;
cmd.Parameters.Add("@picid", SqlDbType.Int).Value = picid;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
*/
}
}
#endregion
#region 数据处理类DataAccess
public class DataAccess
{
#region 属性
protected static SqlConnection conn=new SqlConnection();
protected static SqlCommand comm=new SqlCommand();
#endregion
#region 构造函数
public DataAccess()
{
//init();
}
#endregion
#region 打开数据库连接 openConnection
/*
* 功能:打开数据库连接
* 参数
* ----------------
*无
* ----------------
*/
private static void openConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn.State == ConnectionState.Closed)
{
String Str="";
if(HttpContext.Current.Application["SqlConn"]==null)
Str = "";
else
Str = HttpContext.Current.Application["SqlConn"].ToString();
if(Str == "")
{
Str = System.Configuration.ConfigurationSettings.AppSettings["connString"];
if(Str == null)
Str = "";
HttpContext.Current.Application["Sqlconn"]=Str;
}
conn.ConnectionString = Str;
comm.Connection =conn;
try
{
conn.Open();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}
}
#endregion
#region 关闭当前数据库连接 closeConnection
/*
* 功能:关闭数据库连接
* 参数
* ----------------
* 无
* ----------------
*/
private static void closeConnection()
{
if(conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
#region 数据表格的绑定BindGrid
/*
* 功能:数据表格的绑定
* 参数
* ----------------
* Query: 要查询的SQL语句
* myDataGrid： 要绑定的表格控件
* ----------------
*/
public static void BindGrid(string Query,DataGrid myDataGrid)
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["connString"];
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlDataAdapter myCommand = new SqlDataAdapter(Query, conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "UntilName");
myDataGrid.DataSource=ds.Tables["UntilName"].DefaultView;
myDataGrid.DataBind();
conn.Close();
}
#endregion
#region 数据表格之数据删除、插入操作DisposeRecound
public void DisposeRecound(string Query)
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["connString"];
SqlConnection conn = new SqlConnection(strConn);
SqlCommand myCommand = new SqlCommand(Query,conn);
myCommand.Connection.Open();
try
{
myCommand.ExecuteNonQuery();
}
catch
{
//...
}
myCommand.Connection.Close();
}
#endregion
#region 执行Sql查询语句 ExecuteSql
/*
* 功能:执行特定的SQL语句
* 参数
* ----------------
* sqlstr: 要执行的SQL语句
* ----------------
*/
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
comm.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
#endregion
#region 执行存储过程 ExecutePorcedure
/*
* 功能:执行存储过程
* 参数
* ----------------
* procName: 存储过程名
* coll： SqlParameters 集合
* ----------------
*/
public static void ExecutePorcedure(string procName,SqlParameter[] coll)
{
try
{
openConnection();
for(int i=0;i<coll.Length;i++)
{
comm.Parameters .Add(coll[i]);
}
comm.CommandType=CommandType.StoredProcedure ;
comm.CommandText =procName;
comm.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
#endregion
#region 执行存储过程并返回数据集 ExecutePorcedure
/*
* 功能:执行存储过程并返回数据集
* 参数
* ----------------
* procName: 存储过程名
* coll： SqlParameters 集合
* ds: 需传入一个数据集变量，然后函数对其进行操作，并返回相应数据
* ----------------
*
* 返回值
* ----------------
* ds: 需传入一个数据集变量，然后函数对其进行操作，并返回相应数据
* ----------------
*/
public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds)
{
try
{
SqlDataAdapter da=new SqlDataAdapter();
openConnection();
for(int i=0;i<coll.Length;i++)
{
comm.Parameters .Add(coll[i]);
}
comm.CommandType=CommandType.StoredProcedure ;
comm.CommandText =procName;
da.SelectCommand =comm;
da.Fill(ds);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
#endregion
#region 执行Sql查询语句,同时进行事务处理 ExecuteSqlWithTransaction
/*
* 功能:执行Sql语句,同时进行事务处理
* 参数
* ----------------
* sqlstr: 要查询的SQL语句
* ----------------
*/
public static void ExecuteSqlWithTransaction(string sqlstr)
{
SqlTransaction trans ;
trans=conn.BeginTransaction();
comm.Transaction =trans;
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
comm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
closeConnection();
}
}
#endregion
#region 返回指定Sql语句的SqlDataReader
/*
* 功能:返回指定Sql语句的SqlDataReader
*
* 注意：
* ----------------
* 在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* ----------------
*
* 返回值
* ----------------
* SqlDataReader： SqlDataReader对象
* ----------------
*/
public static SqlDataReader dataReader(string sqlstr)
{
SqlDataReader dr=null;
try
{
openConnection();
comm.CommandText =sqlstr;
comm.CommandType =CommandType.Text ;
dr=comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
dr.Close();
closeConnection();
}
catch
{
}
}
return dr;
}
#endregion
#region 返回指定Sql语句的SqlDataReader
/*
* 功能:返回指定Sql语句的SqlDataReader
*
* 注意：
* ----------------
* 在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* dr: 当参数传入一个SqlDataReader变量，在函数内可对其进行改变赋值
* ----------------
*
* 返回值
* ----------------
* dr: 当参数传入一个SqlDataReader变量，在函数内可对其进行改变赋值
* ----------------
*/
public static void dataReader(string sqlstr,ref SqlDataReader dr)
{
try
{
openConnection();
comm.CommandText =sqlstr;
comm.CommandType =CommandType.Text ;
dr=comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
if(dr!=null && !dr.IsClosed)
dr.Close();
}
catch
{
}
finally
{
closeConnection();
}
}
}
#endregion
#region 返回指定Sql语句的DataSet
/*
* 功能:返回指定Sql语句的DataSet
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* ----------------
*
* 返回值
* ----------------
* 返回一个数据集DataSet
* ----------------
*/
public static DataSet dataSet(string sqlstr)
{
DataSet ds= new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
da.SelectCommand =comm;
da.Fill(ds);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return ds;
}
#endregion
#region 返回指定Sql语句的DataSet
/*
* 功能:返回指定Sql语句的DataSet
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* ds： 传入一个DataSet对象，函数操作时可以改变，并将改变应于于函数之外
* ----------------
*
* 返回值
* ----------------
* ds： 传入一个DataSet对象，函数操作时可以改变，并将改变应于于函数之外
* ----------------
*/
public static void dataSet(string sqlstr,ref DataSet ds)
{
SqlDataAdapter da=new SqlDataAdapter();
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
da.SelectCommand =comm;
da.Fill(ds);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
#endregion
#region 返回指定Sql语句的DataTable
/*
* 功能:返回指定Sql语句的DataSet
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* ----------------
*
* 返回值
* ----------------
* 返回一个内存中的表
* ----------------
*/
public static DataTable dataTable(string sqlstr)
{
SqlDataAdapter da=new SqlDataAdapter();
DataTable datatable=new DataTable();
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
da.SelectCommand =comm;
da.Fill(datatable);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
#endregion
#region 执行指定Sql语句,同时给传入DataTable进行赋值
/*
* 功能:返回指定Sql语句的 DataTable
*
* 参数
* ----------------
* sqlstr: 传入的Sql语句
* dt： 传入一个DataTable变量的引用
* ----------------
*
* 返回值
* ----------------
* dt： 传入一个DataTable变量的引用，函数内操作，并将改变结果返回
* ----------------
*/
public static void dataTable(string sqlstr,ref DataTable dt)
{
SqlDataAdapter da=new SqlDataAdapter();
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
da.SelectCommand =comm;
da.Fill(dt);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
#endregion
#region 执行带参数存储过程并返回数据集合
/*
* 功能:执行带参数存储过程并返回数据集合
*
* 参数
* ----------------
* procName: 存储过程名称
* parameters： SqlParameterCollection 输入参数
* ----------------
*
* 返回值
* ----------------
* 返回数据表格
* ----------------
*/
public static DataTable dataTable(string procName,SqlParameterCollection parameters)
{
SqlDataAdapter da=new SqlDataAdapter();
DataTable datatable=new DataTable();
try
{
openConnection();
comm.Parameters.Clear();
comm.CommandType=CommandType.StoredProcedure ;
comm.CommandText =procName;
foreach(SqlParameter para in parameters)
{
SqlParameter p=(SqlParameter)para;
comm.Parameters.Add(p);
}
da.SelectCommand =comm;
da.Fill(datatable);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
#endregion
#region 返回视图
/*
* 功能:执行带参数存储过程并返回数据集合
*
* 参数
* ----------------
* procName: 存储过程名称
* ----------------
*
* 返回值
* ----------------
* 返回数据视图
* ----------------
*/
public static DataView dataView(string sqlstr)
{
SqlDataAdapter da=new SqlDataAdapter();
DataView dv=new DataView();
DataSet ds=new DataSet();
try
{
openConnection();
comm.CommandType=CommandType.Text;
comm.CommandText =sqlstr;
da.SelectCommand =comm;
da.Fill(ds);
dv=ds.Tables[0].DefaultView;
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dv;
}
#endregion
}
#endregion
}
