using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Configuration;
///
/// DataClass 的摘要说明
///
public class DataClass
{
private SqlConnection GetSqlServerConn()
{
SqlConnection sqlConn; //定义SQL Server连接对象
string strConn = WebConfigurationManager.ConnectionStrings["ch21DataBase"].ConnectionString;
//读取Web.config配置文件的ConnectionString节点获取连接字符串
sqlConn = new SqlConnection(strConn); //生成数据连接对象
sqlConn.Open(); //打开数据库连接
return sqlConn; //返回数据库连接对象以供调用
}
private void CloseSqlServerConn(SqlConnection sqlConn)
{
if(sqlConn.State==ConnectionState.Open) //如果数据库连接处于关闭状态，则打开此连接
{
sqlConn.Close();
}
}
public bool SaveFilesInfo(HttpFileCollection fileColl)
{
SqlConnection sqlConn;
SqlCommand sqlComm;
string strComm = @"insert into tabUploadFile(NewFileName,OldFileName,SaveAddress,UploadTime,TypeName)
value(@NewFileName,@OldFileName,@SaveAddress,@UploadTime,@TypeName)";
sqlConn = GetSqlServerConn(); //调用方法获取数据库连接
SqlTransaction sqlTran = sqlConn.BeginTransaction(); //开始数据库事务
StringBuilder sbFileName;
try
{
sqlComm = new SqlCommand(strComm, sqlConn);
sqlComm.Transaction = sqlTran; //指定命令要使用的事务
for(int i=0;i<fileColl.Count;i++) //循环保存上传信息
{
if(!String.IsNullOrEmpty(fileColl[i].FileName))
{
sbFileName = new StringBuilder(); //使用年月日时分秒毫秒生成文件名
sbFileName.Append(DateTime.Now.Year);
sbFileName.Append(DateTime.Now.Month);
sbFileName.Append(DateTime.Now.Day);
sbFileName.Append(DateTime.Now.Hour);
sbFileName.Append(DateTime.Now.Minute);
sbFileName.Append(DateTime.Now.Second);
sbFileName.Append(DateTime.Now.Millisecond);
sbFileName.Append(Path.GetExtension(fileColl[i].FileName));
sqlComm.Parameters.Clear(); //清空以前参数，指定当前SQL命令参数
sqlComm.Parameters.AddWithValue("@NewFileName", sbFileName.ToString());
sqlComm.Parameters.AddWithValue("@OldFileName", Path.GetFileName(fileColl[i].FileName));
sqlComm.Parameters.AddWithValue("@SaveAddress", System.Web.HttpContext.Current.Server.MapPath("~/Upload/"));
sqlComm.Parameters.AddWithValue("@UploadTime", DateTime.Now);
sqlComm.Parameters.AddWithValue("@TypeName", Path.GetExtension(fileColl[i].FileName));
sqlComm.ExecuteNonQuery(); //执行SQL命令
fileColl[i].SaveAs(System.Web.HttpContext.Current.Server.MapPath("~/Upload/") + sbFileName.ToString());
//保存对应的文件到服务器
}
}
sqlTran.Commit(); //提交事务保存数据
return true;
}
catch(Exception ex)
{
sqlTran.Rollback(); //发生异常回滚事务
CloseSqlServerConn(sqlConn); //关闭数据连接
return false;
}
}
public DataTable GetFilesInfo()
{
SqlConnection sqlConn;
SqlDataAdapter sqlAdpt;
DataTable dtFilesInfo;
string strComm = "select NewFileName,OldFileName,SaveAddress,UploadTime,TypeName from tabUploadFile";
try
{
sqlConn = GetSqlServerConn();
sqlAdpt = new SqlDataAdapter(strComm, sqlConn); //使用数据适配器读取数据
dtFilesInfo = new DataTable();
sqlAdpt.Fill(dtFilesInfo); //填充数据到DataTable
return dtFilesInfo;
}
catch(Exception ex)
{
throw ex;
}
}
public DataClass()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
