protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(Globals.ConnectionString))
{
//add
MyDBTable t = new MyDBTable(conn, "select * from test");
//DataRow dr = t.Table.NewRow();
DataRow dr = t.NewRow();
dr["name"] = "selecttest";
//t.Table.Rows.Add(dr);
t.AddRow(dr);// ==> t.Rows.Add(dr);
Response.Write("newdr.id=" + dr["id"] + "<br>");//可以得到自增id,前提:select 全部数据
t.Update();
Response.Write("newdr.id=" + dr["id"] + "<br>");//可以得到自增id
//update
MyDBTable t2 = new MyDBTable(conn, "select * from test where id=23 ");
if (t2.RowCount > 0) {
t2.Rows[0]["name"] = "new named";
t2.Update();
}
//delete
MyDBTable t3 = new MyDBTable(conn, "select * from test where id=26 ");
if (t3.RowCount > 0)
{
t3.Rows[0].Delete();
t3.Update();
}
return;
// 建立数据库连接.
//SqlConnection conn = new SqlConnection(connString);
//conn.Open();
// 创建一个 DataAdapter 对象，它表示数据库和 DataSet 对象之间的链接。
SqlDataAdapter daTest
= new SqlDataAdapter("Select * From test ", conn);
// DataSet 名字可以自己取一个.
DataSet dsTest = new DataSet("Test");
// SqlDataAdapter 类提供 Fill 和 FillSchema 两种方法，这对于加载这些数据很关键。
// 这两种方法均可将信息加载到 DataSet 中。
// Fill 加载数据本身，而 FillSchema 加载有关特定表的所有可用的元数据（如列名、主键和约束）。
// 处理数据加载的正确方式是先运行 FillSchema，后运行 Fill。
daTest.FillSchema(dsTest, SchemaType.Source, "test");
daTest.Fill(dsTest, "test");
// 这些数据此时作为 DataSet 的 Tables 集合内独立的 DataTable 对象来提供。
// 如果在对 FillSchema 和 Fill 的调用中指定了一个表名，
// 则可以使用该名称访问您需要的特定表。
DataTable dtTest;
dtTest = dsTest.Tables["test"];
// 从 DataTable 获取新的 DataRow 对象。
DataRow drCurrent = dtTest.NewRow();
// 根据需要设置 DataRow 字段值。
drCurrent["name"] = "T";
// 将新的对象传递给 DataTable.Rows 集合的 Add 方法。
dtTest.Rows.Add(drCurrent);
Response.Write("newdr.drCurrent=" + drCurrent["id"] + "<br>");
// 通过 SqlCommandBuilder 设置 DataAdapter 对象的 InsertCommand、UpdateCommand 和 DeleteCommand 属性。
// 注意：表必须有主键信息
SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(daTest);
// 更新原始数据库，可将 DataSet 传递到 DataAdapter 对象的 Update 方法。
daTest.Update(dsTest, "test");
//Console.WriteLine("新增测试数据完毕， 去查询一下，数据库是否已经成功新增数据！");
//Console.WriteLine("按回车键继续...");
//Console.ReadLine();
Response.Write("newdr.drCurrent=" + drCurrent["id"] + "<br>");
}
}
private void ProcFile(SqlConnection conn, CommonDAL cdal , FileInfo NextFile)
{
log.Info(logPrefix + "ProcFolder[" + NextFile.Name + "]");
//any files in the m folder:
//1.mv to dest;
//2.calc md5/sha1/sha265
string destdir = "", relpath = "", dupdir = "";
string srcfilename = "", destfilename = "";
long filesize = 0;
string fileext = "";
string md5 = "", sha1 = "", sha256 = "";
string sql = "";
try
{
srcfilename = NextFile.FullName;
////db中是否已经存在该文件名DriversFiles 和 Server_D_DriversFiles(老数据)
//sql = "select count(*) as cnt from DriversFiles where [FileName]='" + NextFile.Name + "'";
//int cnt = Functions.ParseInt(cdal.GetFieldValueStringBySQL(sql, "cnt"), 0);
//sql = "select count(*) as cnt from Server_D_DriversFiles where [FileName]='" + NextFile.Name + "'";
//int cnt0 = Functions.ParseInt(cdal.GetFieldValueStringBySQL(sql, "cnt"), 0);
//log.Info(logPrefix + "db已存在FileName[" + NextFile.Name + "]数量(新):" + cnt + ";(老):" + cnt0);
//if (cnt + cnt0 > 0)
//2016-3-8 修改为后入为主的策略,Server_D_DriversFiles 存在于此就修改此表,DriversFiles 存在于此就修改此表,否则就新加到DriversFiles ;
sql = "select * from Server_D_DriversFiles where [FileName]='" + NextFile.Name + "'";
DataSet dsOld = cdal.GetDS(sql);
sql = "select * from DriversFiles where [FileName]='" + NextFile.Name + "'";
DataSet dsNew = cdal.GetDS(sql);
if (dsOld.Tables[0].Rows.Count > 0) //存在于 Server_D_DriversFiles
{
log.Info(logPrefix + "File[" + NextFile.Name + "] 存在于Server_D_DriversFiles;");
#region 先入为主的策略 (否决)
////若存在记录,则忽略该记录,move to dup dir记录日志;
////move to dupdir
//log.Info(logPrefix + "FileName[" + NextFile.Name + "] Has Existed in System and Will Be Moved to dup!");
//dupdir = m_MonitorFolder + @"\dup\";
//if (!Directory.Exists(dupdir)) Directory.CreateDirectory(dupdir);
//File.Move(srcfilename, dupdir + NextFile.Name);
////continue;
//return;
#endregion
relpath = ""; // 2016030811223308096/
string F_Path = dsOld.Tables[0].Rows[0]["F_Path"].ToString(); // /DriverFilesUploadFloder/server/2008101712345610916/3008ESASHBAREDHATAS40X86.exe
if (!F_Path.StartsWith("/DriverFilesUploadFloder/server/")) {
log.Error(logPrefix + "db.Server_D_DriversFiles.F_Path[" + F_Path + "] not begin with /DriverFilesUploadFloder/server/,无法处理此文件. ");
return;
}
string[] arrF_Path = F_Path.Split("/".ToCharArray());
if (arrF_Path.Length < 4) {
log.Error(logPrefix + "db.Server_D_DriversFiles.F_Path[" + F_Path + "] 格式错误,少于4段. ");
return;
}
relpath = arrF_Path[arrF_Path.Length - 2];
log.Info(logPrefix + "get relpath [" + relpath + "] from db. ");
//创建目标目录
destdir = m_DestFolderRoot + "/" + relpath;
log.Info(logPrefix + "创建目标目录:" + destdir);
if (!Directory.Exists(destdir)) Directory.CreateDirectory(destdir);
destfilename = destdir + NextFile.Name;
//move file to dest
if (File.Exists(destfilename)) File.Delete(destfilename);
File.Move(srcfilename, destfilename);
log.Info(logPrefix + "File[" + srcfilename + "] move to Dest;");
//get filesize
filesize = NextFile.Length;
log.Info(logPrefix + "Dest File[" + destfilename + "];filesieze:[" + filesize + "];");
if (NextFile.Extension.Length > 1)
fileext = NextFile.Extension.Substring(1, NextFile.Extension.Length - 1);
//get filecodes
md5 = GetFileCode(destfilename, "md5");
sha1 = GetFileCode(destfilename, "sha1");
sha256 = GetFileCode(destfilename, "sha256");
log.Info(logPrefix + "Dest File[" + destfilename + "];Calc==> md5:[" + md5 + "],sha1:[" + sha1 + "],sha256:[" + sha256 + "];");
//update to db Server_D_DriversFiles
sql = " update Server_D_DriversFiles set md5='" + md5 + "', sha1='" + sha1 + "', sha256='" + sha256 + "',uploadtime=getdate() where FileName='" + NextFile.Name + "'";
cdal.ExecSQL(sql);
log.Info(logPrefix + "update 1 table Server_D_DriversFiles ;");
}
else if (dsNew.Tables[0].Rows.Count > 0) //存在于 DriversFiles
{
log.Info(logPrefix + "File[" + NextFile.Name + "] 存在于DriversFiles;");
relpath = ""; // 2016030811223308096/
string F_Path = dsNew.Tables[0].Rows[0]["F_Path"].ToString(); // /DriverFilesUploadFloder/server/2008101712345610916/3008ESASHBAREDHATAS40X86.exe
if (!F_Path.StartsWith("/DriverFilesUploadFloder/server/"))
{
log.Error(logPrefix + "db.DriversFiles.F_Path[" + F_Path + "] not begin with /DriverFilesUploadFloder/server/,无法处理此文件. ");
return;
}
string[] arrF_Path = F_Path.Split("/".ToCharArray());
if (arrF_Path.Length < 4)
{
log.Error(logPrefix + "db.DriversFiles.F_Path[" + F_Path + "] 格式错误,少于4段. ");
return;
}
relpath = arrF_Path[arrF_Path.Length - 2];
log.Info(logPrefix + "get relpath [" + relpath + "] from db. ");
//创建目标目录
destdir = m_DestFolderRoot + "/" + relpath;
log.Info(logPrefix + "创建目标目录:" + destdir);
if (!Directory.Exists(destdir)) Directory.CreateDirectory(destdir);
destfilename = destdir + NextFile.Name;
//move file to dest
if (File.Exists(destfilename)) File.Delete(destfilename);
File.Move(srcfilename, destfilename);
log.Info(logPrefix + "File[" + srcfilename + "] move to Dest;");
//get filesize
filesize = NextFile.Length;
log.Info(logPrefix + "Dest File[" + destfilename + "];filesieze:[" + filesize + "];");
if (NextFile.Extension.Length > 1)
fileext = NextFile.Extension.Substring(1, NextFile.Extension.Length - 1);
//get filecodes
md5 = GetFileCode(destfilename, "md5");
sha1 = GetFileCode(destfilename, "sha1");
sha256 = GetFileCode(destfilename, "sha256");
log.Info(logPrefix + "Dest File[" + destfilename + "];Calc==> md5:[" + md5 + "],sha1:[" + sha1 + "],sha256:[" + sha256 + "];");
//update to db DriversFiles and Server_D_DriversFiles
sql = " update DriversFiles set md5='" + md5 + "', sha1='" + sha1 + "', sha256='" + sha256 + "',uploadtime=getdate(),F_OldPath='"+srcfilename+"' where FileName='" + NextFile.Name + "'";
sql = sql + " update Server_D_DriversFiles set md5='" + md5 + "', sha1='" + sha1 + "', sha256='" + sha256 + "',uploadtime=getdate() where FileName='" + NextFile.Name + "'";
cdal.ExecSQL(sql);
log.Info(logPrefix + "update 2 tables DriversFiles and Server_D_DriversFiles ;");
}
else
{
relpath = (new Random()).Next(99999).ToString().PadLeft(5, '0') + "/"; // 08096/
relpath = DateTime.Now.ToString("yyyyMMddHHmmss") + relpath; // 2016030811223308096/
//创建目标目录
destdir = m_DestFolderRoot + "/" + relpath;
log.Info(logPrefix + "创建目标目录:" + destdir);
if (!Directory.Exists(destdir)) Directory.CreateDirectory(destdir);
destfilename = destdir + NextFile.Name;
//move file to dest
//若文件还在上传中是否会引起一个异常退出?tbd
if (File.Exists(destfilename)) File.Delete(destfilename);
File.Move(srcfilename, destfilename);
log.Info(logPrefix + "File[" + srcfilename + "] move to Dest;");
//get filesize
filesize = NextFile.Length;
log.Info(logPrefix + "Dest File[" + destfilename + "];filesieze:[" + filesize + "];");
if (NextFile.Extension.Length > 1)
fileext = NextFile.Extension.Substring(1, NextFile.Extension.Length - 1);
//get filecodes
md5 = GetFileCode(destfilename, "md5");
sha1 = GetFileCode(destfilename, "sha1");
sha256 = GetFileCode(destfilename, "sha256");
log.Info(logPrefix + "Dest File[" + destfilename + "];Calc==> md5:[" + md5 + "],sha1:[" + sha1 + "],sha256:[" + sha256 + "];");
//rec to db DriversFiles
/*
[FileName] [varchar](500) NOT NULL,
[NFileName] [varchar](500) NOT NULL,
[FileExt] [varchar](50) NULL,
[FileType] [varchar](200) NULL,
[FileSize] [numeric](18, 0) NULL,
[ISDriver] [varchar](1) NULL,
[UploadBy] [varchar](200) NULL,
[UploadTime] [datetime] NULL,
[F_Path] [varchar](2000) NULL,
[F_DownloadTimes] [int] NULL,
[F_URL] [varchar](500) NULL,
[F_OldPath] [varchar](500) NULL,
[FormatFileSize] [numeric](18, 2) NULL,
[md5] [nvarchar](100) NULL,
[sha1] [nvarchar](100) NULL,
[sha256] [nvarchar](100) NULL,
*/
MyDBTable t = new MyDBTable(conn, "select * from DriversFiles");//可以得到自增id,前提:select 全部数据
//DataRow dr = t.Table.NewRow();
DataRow dr = t.NewRow();
dr["FileName"] = NextFile.Name;
dr["NFileName"] = NextFile.Name;
dr["FileExt"] = fileext;
dr["FileType"] = dr["FileExt"];
dr["FileSize"] = filesize;
dr["ISDriver"] = "1"; // ???
dr["UploadBy"] = "administrator";//tbd...
dr["UploadTime"] = DateTime.Now;
dr["F_Path"] = "/DriverFilesUploadFloder/server/" + relpath + NextFile.Name; // /DriverFilesUploadFloder/server/2008101712345610916/3008ESASHBAREDHATAS40X86.exe
dr["F_DownloadTimes"] = 0;
dr["F_URL"] = "http://driverdl.lenovo.com.cn/lenovo";
dr["F_OldPath"] = srcfilename;// ""; // E:\3008ESASHBAREDHATAS40X86.exe //2016-4-7 用作存 源头目录
dr["FormatFileSize"] = filesize / 1024;
dr["md5"] = md5;
dr["sha1"] = sha1;
dr["sha256"] = sha256;
//t.Table.Rows.Add(dr);
t.AddRow(dr);// ==> t.Rows.Add(dr);
t.Update();
log.Info(logPrefix + "Dest File[" + destfilename + "];Rec in DB;");
}//if 存在与否
}
catch (IOException ioe)
{
log.Error(logPrefix + "Proc File IOError:" + ioe.ToString());
//@20160327
//这个错误将发生在move过程;若此时source还没有完全落地,将会被另一个进程占用(ftp),但此时已经建立了destdir需要删掉;
if (Directory.Exists(destdir)) Directory.Delete(destdir);
}
catch (Exception e)
{
log.Error(logPrefix + "Proc File Error:" + e.ToString());
}
}