public static DataTable GetListLanguage(int _startIndex, int _pageNumberRow)
{
DataTable dt = new DataTable();
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string tempStr = "SELECT * FROM Languages";
dt = db.DoSQLSelect(tempStr, _startIndex, _pageNumberRow);
return(dt);
}
public static DataTable GetListLanguage()
{
DataTable dt = new DataTable();
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string tempStr = "SELECT * FROM Languages";
dt = db.DoSQLSelect(tempStr);
return(dt);
}
public int getTotalRecord(string _languageId)
{
int total = 0;
string tempStr = "SELECT count(*) FROM Resources WHERE languageId='" + _languageId + "'";
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string value = db.GetSingleValueSelect(tempStr);
try { total = Int16.Parse(value); } catch (Exception exc) { }
return(total);
}
public DataTable listFormContent(int formID)
{
SQLServerDBHelper sqlHelper = new SQLServerDBHelper();
DataTable tb = new DataTable();
//sqlHelper.DoSQLSelect
string sqlQuery = @"select * from FormContent where FormID =" + formID + "";
tb = sqlHelper.DoSQLSelect(sqlQuery);
return(tb);
}
public JsonResult GetListSite()
{
var listSite = new List <Tuple <string, string> >();
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
DataTable dt = db.DoSQLSelect("SELECT * FROM Site");
foreach (DataRow dr in dt.Rows)
{
listSite.Add(Tuple.Create(dr["SiteID"].ToString().Trim(), dr["SiteName"].ToString().Trim()));
}
return(Json(listSite, JsonRequestBehavior.AllowGet));
}
public JsonResult GetListBu(string site)
{
var listBU = new List <Tuple <string, string> >();
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
DataTable dt = db.DoSQLSelect("SELECT * FROM BU WHERE SiteID='" + site.Trim() + "'");
foreach (DataRow dr in dt.Rows)
{
listBU.Add(Tuple.Create(dr["BUID"].ToString().Trim(), dr["BUName"].ToString().Trim()));
}
return(Json(listBU, JsonRequestBehavior.AllowGet));
}
public static List <SiteModel> GetListSite()
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string tempStr = "SELECT * FROM Site";
DataTable dt = db.DoSQLSelect(tempStr);
List <SiteModel> ListSite = new List <SiteModel>();
foreach (DataRow dr in dt.Rows)
{
ListSite.Add(new SiteModel(dr["SiteID"].ToString().Trim(), dr["SiteName"].ToString().Trim()));
}
return(ListSite);
}
public static List <string> GetListType()
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string tempStr = "SELECT ApproverTypeID FROM ApproverType";
DataTable dt = db.DoSQLSelect(tempStr);
List <string> ListType = new List <string>();
foreach (DataRow dr in dt.Rows)
{
ListType.Add(dr[0].ToString().Trim());
}
return(ListType);
}
public static bool Login(string _userId, string _password)
{
SQLServerDBHelper db = new SQLServerDBHelper("ESign");
string tempSQL = "SELECT * FROM Account UserID='" + _userId + "' AND Password='******'";
DataTable dt = db.DoSQLSelect(tempSQL);
if (dt != null && dt.Rows.Count > 0)
{
Login(dt.Rows[0]["UserID"].ToString(), dt.Rows[0]["Username"].ToString(), dt.Rows[0]["Permission"].ToString());
return(true);
}
return(false);
}
public void DeleteApprover(string ApproverID)
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string deleteSql = "DELETE Approver WHERE ApproverID='" + ApproverID.Trim() + "'";
if (db.ExcuteNonQuery(deleteSql))
{
Ultils.WriteCookie("Success", LanguageHelper.GetResource("DeleteSuccess"));
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("DeleteFail"));
}
}
public bool IsExistResource(string _languageId, string _resourceName)
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string checkSql = "SELECT * FROM Resources WHERE languageId='" + _languageId.Trim() + "' AND resourceName='" + _resourceName.Trim() + "'";
DataTable dt = db.DoSQLSelect(checkSql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private int DDL_Operation_View(ref DataTable dt, ref string select_Result, string source_DBName_f, string source_DBName, string dest_DBName_f, string dest_DBName)
{
int i;
string getDBInfoToStore_View = "[SP_Cfg_GetViewDefinition]";
string generateScriptToStore_View = "[SP_Dey_CreateViewScriptProc]";
string str_Type = "V";
string str_FileName = "View";
string operation_Type = "视图";
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, getDBInfoToStore_View); //获得定义的信息，存入配置库
txbResult.AppendText("视图：取“" + source_DBName_f + "”中视图的定义信息，并存入“" + dest_DBName_f + "”的操作成功\r\n\r\n");
SQLServerDBHelper.GetDBInfoToStore(generateScriptToStore_View); //生成创建元数据的脚本
txbResult.AppendText("视图：生成创建“" + source_DBName_f + "”中视图的元数据的脚本操作成功\r\n\r\n");
i = ScriptToStore(ref dt, ref select_Result, str_Type, str_FileName, operation_Type, dest_DBName_f);
return(i);
}
private int DDL_Operation_Function(ref DataTable dt, ref string select_Result, string source_DBName_f, string source_DBName, string dest_DBName_f, string dest_DBName)
{
int i;
string getDBInfoToStore_Function = "[SP_Cfg_GetFunctionDefinition]";
string generateScriptToStore_Function = "[SP_Dey_CreateFunctionMetaScript]";
string str_Type = "F";
string str_FileName = "Function";
string operation_Type = "函数";
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, getDBInfoToStore_Function); //获得定义的信息，存入配置库
txbResult.AppendText("函数：取“" + source_DBName_f + "”中函数的定义信息，并存入“" + dest_DBName_f + "”的操作成功\r\n\r\n");
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, generateScriptToStore_Function); //生成创建元数据的脚本
txbResult.AppendText("函数：生成创建“" + source_DBName_f + "”中函数的元数据的脚本操作成功\r\n\r\n");
i = ScriptToStore(ref dt, ref select_Result, str_Type, str_FileName, operation_Type, dest_DBName_f);
return(i);
}
private int DDL_Operation_Type(ref DataTable dt, ref string select_Result, string source_DBName_f, string source_DBName, string dest_DBName, string dest_DBName_f)
{
int i;
//string getDBInfoToStore_Type = "[]";
string generateScriptToStore_Type = "[SP_Dey_CreateTypeScriptProc]";
string str_Type = "Y";
string str_FileName = "Type";
string operation_Type = "类型";
//SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, getDBInfoToStore_Type); //获得定义的信息，存入配置库
//txbResult.AppendText("类型：取“" + source_DBName_f + "”中类型的定义信息，并存入“" + dest_DBName_f + "”的操作成功\r\n\r\n");
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, generateScriptToStore_Type); //生成创建元数据的脚本
txbResult.AppendText("类型：生成创建“" + source_DBName_f + "”中类型的元数据的脚本操作成功\r\n\r\n");
i = ScriptToStore(ref dt, ref select_Result, str_Type, str_FileName, operation_Type, dest_DBName_f);
return(i);
}
public static bool UpdateUser(string UserID, string Password, string Username, string Email, string Telephone, string CostNo, string Department, string BUID)
{
SQLServerDBHelper db = new SQLServerDBHelper("ESign");
string tempSQL = "UPDATE Account SET Password=@pw,Username=@un,Email=@em,Telephone=@te,CostNo=@cn,Department=@dept,BUID=@bu WHERE UserID=@id";
SqlParameter[] parameters = new SqlParameter[8];
parameters.SetValue(new SqlParameter("pw", Password), 0);
parameters.SetValue(new SqlParameter("un", Username), 1);
parameters.SetValue(new SqlParameter("em", Email), 2);
parameters.SetValue(new SqlParameter("te", Telephone), 3);
parameters.SetValue(new SqlParameter("cn", CostNo), 4);
parameters.SetValue(new SqlParameter("dept", Department), 5);
parameters.SetValue(new SqlParameter("bu", BUID), 6);
parameters.SetValue(new SqlParameter("id", UserID), 7);
return(db.ExcuteNonQuery(tempSQL, parameters));
}
public bool DeleteResource(string _languageId, string _resourceName)
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string deleteSql = "DELETE Resources WHERE resourceName='" + _resourceName.Trim() + "' AND languageId='" + _languageId.Trim() + "'";
if (db.ExcuteNonQuery(deleteSql))
{
Ultils.WriteCookie("Success", LanguageHelper.GetResource("DeleteSuccess"));
return(true);
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("DeleteFail"));
}
return(false);
}
private int DDL_Operation_Procedure(ref DataTable dt, ref string select_Result, string source_DBName_f, string source_DBName, string dest_DBName_f, string dest_DBName)
{
int i;
//存储过程的操作存入数据库
string getDBInfoToStore_Procedure = "[SP_Cfg_GetProcedureDefinition]";
string generateScriptToStore_Procedure = "[SP_Dey_CreateProcedureMetaScript]";
string str_Type = "P";
string str_FileName = "Procedure";
string operation_Type = "存储过程";
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, getDBInfoToStore_Procedure); //获得定义的信息，存入配置库
txbResult.AppendText("存储过程：取“" + source_DBName_f + "”中存储过程的定义信息，并存入“" + dest_DBName_f + "”的操作成功\r\n\r\n");
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, generateScriptToStore_Procedure); //生成创建元数据的脚本
txbResult.AppendText("存储过程：生成创建“" + source_DBName_f + "”中存储过程的元数据的脚本操作成功\r\n\r\n");
i = ScriptToStore(ref dt, ref select_Result, str_Type, str_FileName, operation_Type, dest_DBName_f);
return(i);
}
/// <summary>
/// 目标数据库测试连接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnTestTarget_Click(object sender, EventArgs e)
{
string db_Target = cbxTargetDataBases.Text.Trim();
if (SQLServerDBHelper.TestResult(db_Target) == true)
{
labTarget.Text = "连接成功";
labTarget.ForeColor = System.Drawing.Color.Green;
txbResult.AppendText("数据库“" + db_Target + "”连接测试成功\r\n\r\n");
}
else
{
labTarget.Text = "连接失败";
labTarget.ForeColor = System.Drawing.Color.Red;
txbResult.AppendText("数据库“" + db_Target + "”连接测试失败\r\n\r\n");
}
}
/// <summary>
/// Lấy giá trị string của resource theo ngôn ngữ chỉ định.
/// Các giá trị này được lưu trong database tb_Resource
/// </summary>
/// <param name="_resourceId">Mã resource. VD: Home.LabelLanguage; Account.Username; Account.Password; ...</param>
/// <param name="_languageId">Mã ngôn ngữ cần lấy giá trị. Tiếng Anh: en, Tiếng Việt: vi, Tiếng Trung: zh</param>
/// <returns> Giá trị text </returns>
public static string GetResource(string _resourceId, string _languageId, bool _notFoundAdd = false)
{
string value = _resourceId;
if (_notFoundAdd)
{
value = _languageId + "." + value;
}
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string tempStr = "SELECT resourceValue FROM Resources WHERE languageId='" + _languageId + "' AND resourceName='" + _resourceId + "'";
tempStr = db.GetSingleValueSelect(tempStr);
if (!string.IsNullOrWhiteSpace(tempStr))
{
value = tempStr;
}
return(value);
}
public static List <BusinessUnitModel> GetListBU(string SiteID)
{
string tempStr = "SELECT * FROM BU";
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
DataTable dt = db.DoSQLSelect(tempStr);
if (!string.IsNullOrWhiteSpace(SiteID))
{
tempStr += " WHERE SiteID='" + SiteID + "'";
}
dt = db.DoSQLSelect(tempStr);
List <BusinessUnitModel> ListBU = new List <BusinessUnitModel>();
foreach (DataRow dr in dt.Rows)
{
ListBU.Add(new BusinessUnitModel(dr["BUID"].ToString().Trim(), dr["BUName"].ToString().Trim()));
}
return(ListBU);
}
public ApproverDetailModel GetApproverDetail(string ApproverID)
{
ApproverDetailModel adm = new ApproverDetailModel();
string tempStr = "SELECT * FROM Approver WHERE ApproverID='" + ApproverID + "'";
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
DataTable dt = db.DoSQLSelect(tempStr);
adm.ApproverModel = new ApproverModel();
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
adm.ApproverModel = new ApproverModel(dr["ApproverID"].ToString(), dr["SiteID"].ToString().Trim(), dr["BUID"].ToString(), "", dr["ApproverType"].ToString().Trim(), dr["ApproverEmpNo"].ToString().Trim(), dr["ApproverEmpName"].ToString().Trim(), dr["SetupEmp"].ToString().Trim(), dr["SetupTime"].ToString());
adm.SelectedSite = dr["SiteID"].ToString().Trim();
adm.SelectedBU = dr["BUID"].ToString().Trim();
adm.SelectedType = dr["ApproverType"].ToString().Trim();
}
RepairSelectListModel(adm);
return(adm);
}
public void CreateApprover(ApproverModel am)
{
if (am != null)
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string InsertSQL = "INSERT INTO Approver(SiteID, BUID, ApproverType, ApproverEmpNo, ApproverEmpName, SetupEmp) VALUES('" + am.SiteId + "','" + am.BUID + "',N'" + am.ApproverType + "','" + am.ApproverEmpNo + "',N'" + am.ApproverEmpName + "','" + Ultils.GetCookie("UserId") + "')";
if (db.ExcuteNonQuery(InsertSQL))
{
Ultils.WriteCookie("Success", LanguageHelper.GetResource("AddSuccess"));
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("AddFail"));
}
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("ApproverUpdateEmpty"));
}
}
public void UpdateApprover(ApproverModel am)
{
if (am != null && !string.IsNullOrWhiteSpace(am.ApproverID))
{
SQLServerDBHelper db = new SQLServerDBHelper("EsignDB");
string UpdateSql = "UPDATE Approver SET SiteID='" + am.SiteId + "',BUID='" + am.BUID + "',ApproverType=N'" + am.ApproverType + "',ApproverEmpNo='" + am.ApproverEmpNo + "',ApproverEmpName=N'" + am.ApproverEmpName + "',SetupEmp='" + Ultils.GetCookie("UserId") + "',SetupTime=getDate() WHERE ApproverID='" + am.ApproverID.Trim() + "'";
if (db.ExcuteNonQuery(UpdateSql))
{
Ultils.WriteCookie("Success", LanguageHelper.GetResource("UpdateSuccess"));
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("UpdateFail"));
}
}
else
{
Ultils.WriteCookie("Error", LanguageHelper.GetResource("ApproverUpdateEmpty"));
}
}
public static bool CreateUser(string UserID, string Password, string Username, string Email, string Telephone, string CostNo, string Department, string BUID, string SiteID, string ManagerName, string ManagerEmpNo, string ManagerEmail, string Purpose, string Note, string Permission = "user")
{
SQLServerDBHelper db = new SQLServerDBHelper("ESign");
string tempSQL = "INSERT INTO Account (UserID,Password,Username,Email,Telephone,CostNo,Department,BUID,SiteID,ManagerName,ManagerEmpNo,ManagerEmail,Purpose,Note,Permission) VALUES (@id,@pw,@un,@em,@te,@cn,@dept,@bu,@site,@mn,@men,@me,@pur,@note,@per)";
SqlParameter[] parameters = new SqlParameter[15];
parameters.SetValue(new SqlParameter("id", UserID), 0);
parameters.SetValue(new SqlParameter("pw", Password), 1);
parameters.SetValue(new SqlParameter("un", Username), 2);
parameters.SetValue(new SqlParameter("em", Email), 3);
parameters.SetValue(new SqlParameter("te", Telephone), 4);
parameters.SetValue(new SqlParameter("cn", CostNo), 5);
parameters.SetValue(new SqlParameter("dept", Department), 6);
parameters.SetValue(new SqlParameter("bu", BUID), 7);
parameters.SetValue(new SqlParameter("site", SiteID), 8);
parameters.SetValue(new SqlParameter("mn", ManagerName), 9);
parameters.SetValue(new SqlParameter("men", ManagerEmpNo), 10);
parameters.SetValue(new SqlParameter("me", ManagerEmail), 11);
parameters.SetValue(new SqlParameter("pur", Purpose), 12);
parameters.SetValue(new SqlParameter("note", Note), 13);
parameters.SetValue(new SqlParameter("per", Permission), 14);
return(db.ExcuteNonQuery(tempSQL, parameters));
}
/// <summary>
/// 取数据库中的数据，写到文件中
/// </summary>
/// <param name="dt">查询的数据</param>
/// <param name="select_Result">转换后的数据</param>
/// <param name="str_Type">查询的类型</param>
/// <param name="str_FileName">文件名</param>
/// <returns></returns>
private int ScriptToStore(ref DataTable dt, ref string select_Result, string str_Type, string str_FileName, string op_Type, string source_DBName_f)
{
int i;
//创建SQL文件夹
string fp = "d:\\SQL\\" + DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月";
select_Result = "";
Directory.CreateDirectory(fp);
DirectoryInfo dir = new DirectoryInfo(fp);
if (!File.Exists(fp))
{
dir.Create();
}
//把数据库中的数据存入文件中
dt.Clear();
dt = SQLServerDBHelper.GetInfoStoreFile(str_Type);
txbResult.AppendText("" + op_Type + "：获取数据库中的数据成功\r\n\r\n");
for (i = 0; i < dt.Rows.Count; i++)//取出的数据连成字符串
{
select_Result += dt.Rows[i][0].ToString();
}
txbResult.AppendText("" + op_Type + "：获取的数据转换成功\r\n\r\n");
string dateTime = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string filePath = fp + @"\" + str_FileName + "_" + source_DBName_f + "_" + dateTime + ".sql";//定义文件名
txbResult.AppendText("" + op_Type + "：文件存储的完整路径：" + filePath + "\r\n\r\n");
FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write, FileShare.Read);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);//写入文件中
fs.SetLength(0);
sw.WriteLine(select_Result);
sw.Close();
txbResult.AppendText("" + op_Type + "：数据存储完成\r\n\r\n");
return(i);
}
/// <summary>
/// 将某个表相关的整个模板加入参数进行生成
/// </summary>
/// <param name="SourcePath"></param>
/// <param name="DestinationPath"></param>
/// <param name="TableName"></param>
public static void GenerateCodesInDir(string SourcePath, string DestinationPath, Tuple <string, string, string> t3)
{
string tableName = t3.Item1;
string tableConnectionString = t3.Item2;
string tableModel = t3.Item3;
string tableDescription = t3.Item3;
DataTable tableInfo = SQLServerDBHelper.GetTableInfo(tableConnectionString, tableName);
//string
string fieldCode = GenerateFieldCode(tableInfo);
//创建所有文件夹
foreach (string dirPath in Directory.GetDirectories(SourcePath, "*",
SearchOption.AllDirectories))
{
Directory.CreateDirectory(dirPath.Replace(SourcePath, DestinationPath));
}
//套用模板并且复制文件到新的备份目录
foreach (string newPath in Directory.GetFiles(SourcePath, "*.*",
SearchOption.AllDirectories))
{
//文件模板套用
string content = File.ReadAllText(newPath);
content = content.Replace("##Author##", Dns.GetHostName())
.Replace("##DateTime##", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
.Replace("##TableDescription##", tableModel)
.Replace("##ModelName##", tableModel)
.Replace("##Fields##", fieldCode)
.Replace("##TableName##", tableName)
.Replace("##ConnectionString_Key##", findConnSetting(t3.Item2));
//替换文件名
File.WriteAllText(newPath.Replace(SourcePath, DestinationPath).Replace("Order", tableModel)
.Replace(".template", ".cs"), content);
}
}
/// <summary>
/// 绑定源数据库和目标数据库
/// </summary>
public void Load()
{
DataTable dt_Source = new DataTable();
string cmdString_Source = "select [DatabaseName] from Cfg_Bridge where IsSourceDb=1";
dt_Source = SQLServerDBHelper.GetDataBaseName(cmdString_Source);
if (dt_Source != null)
{
cbxSourceDataBases.DataSource = dt_Source;
cbxSourceDataBases.DisplayMember = "DatabaseName";
txbResult.AppendText("源数据库绑定成功\r\n\r\n");
}
DataTable dt_Target = new DataTable();
string cmdString_Target = "select [DatabaseName] from Cfg_Bridge where IsSourceDb=0";
dt_Target = SQLServerDBHelper.GetDataBaseName(cmdString_Target);
if (dt_Target != null)
{
cbxTargetDataBases.DataSource = dt_Target;
cbxTargetDataBases.DisplayMember = "DatabaseName";
txbResult.AppendText("目标数据库绑定成功\r\n\r\n");
}
DataTable dt_DBName_DML = new DataTable();
string cmdString_DBName_DML = "select tablename,comment from Cfg_TableNameForLoadData ";
dt_DBName_DML = SQLServerDBHelper.GetDataBaseName(cmdString_DBName_DML);
if (dt_DBName_DML != null)
{
DataGridViewCheckBoxColumn checkBox = new DataGridViewCheckBoxColumn();
checkBox.HeaderText = "选择";
dgv_DataBaseName.Columns.Insert(0, checkBox);
dgv_DataBaseName.DataSource = dt_DBName_DML;
dgv_DataBaseName.Columns[0].Width = 40;
}
}
private int DDL_Operation_Table(ref DataTable dt, ref string select_Result, string source_DBName, string dest_DBName, string dest_DBName_f)
{
int i = -1;
//string getDBInfoToStore_Table = "[]";
string generateScriptToStore_Table = "[SP_Dey_CreateTableScriptMain]";
//SQLServerDBHelper.GenerateScriptToStore(source_DBName, dest_DBName, getDBInfoToStore_Table); //获得定义的信息，存入配置库
//txbResult.AppendText("表：取“" + source_DBName_f + "”中表的定义信息，并存入“" + dest_DBName_f + "”的操作成功\r\n\r\n");
txbResult.AppendText("操作比较多！请稍等，让它跑一会！\r\n\r\n");
string log_Information = SQLServerDBHelper.GetTableInfoToStore(source_DBName, dest_DBName, generateScriptToStore_Table);//生成创建元数据的脚本
string[] log_Information_new = log_Information.Split(new char[] { '@' });
for (int j = 0; j < log_Information_new.Length - 1; j++)
{
txbResult.AppendText("表：" + log_Information_new[j] + "\r\n\r\n");
}
//创建SQL文件夹
string fp = "d:\\SQL\\" + DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月";
Directory.CreateDirectory(fp);
DirectoryInfo dir = new DirectoryInfo(fp);
if (!File.Exists(fp))
{
dir.Create();
}
string str_Type = "T,M,N,A,S,R,L";
string str_FileName = "Table";
select_Result = "";
string[] str_type_new = str_Type.Split(new char[] { ',' });
string dateTime = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string filePath = fp + @"\" + str_FileName + "_" + dest_DBName_f + "_" + dateTime + ".sql";//定义文件名
for (int k = 0; k < str_type_new.Length; k++)
{
//把数据库中的数据存入文件中
dt.Clear();
dt = SQLServerDBHelper.GetInfoStoreFile(str_type_new[k]);
txbResult.AppendText("表：获取数据库中" + str_type_new[k] + "类型的数据成功\r\n\r\n");
for (i = 0; i < dt.Rows.Count; i++)//取出的数据连成字符串
{
select_Result += dt.Rows[i][0].ToString();
txbResult.AppendText("表：获取的" + str_type_new[k] + "类型数据转换成功\r\n\r\n");
if (!File.Exists(filePath))
{
FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write, FileShare.Read);
StreamWriter sw = new StreamWriter(fs);//写入文件中
fs.SetLength(0);
sw.WriteLine(select_Result);
sw.Close();
}
else
{
FileStream fst = new FileStream(filePath, FileMode.Open, FileAccess.Write, FileShare.Read);
StreamWriter swt = new StreamWriter(fst);
fst.SetLength(0);
swt.WriteLine(select_Result);
swt.Close();
}
}
txbResult.AppendText("表：" + str_type_new[k] + "类型的数据存储完成\r\n\r\n");
}
txbResult.AppendText("表：文件存储的完整路径：" + filePath + "\r\n\r\n");
txbResult.AppendText("表：所以数据存储完成\r\n\r\n");
return(i);
}
//lay du lieu trang ky don
private ApprovalApp getApprovalApp(string appNo)
{
ApprovalApp aprApp = new ApprovalApp();
string sqlQuery = @"select * from APPROVE_SIGN where APPNO = '" + appNo + "'";
SQLServerDBHelper dBHelper = new SQLServerDBHelper("EsignDB");
DataTable tb = dBHelper.DoSQLSelect(sqlQuery);
string sqlQuery1 = @"select * from DATA_APP_ESIGN where APPNO = '" + appNo + "'";
DataTable tb1 = dBHelper.DoSQLSelect(sqlQuery1);
//aprApp.EmpModels = new EmpModel();
// don nguoi dung lam
EmpModel empM = new EmpModel();
if (tb1.Rows.Count > 0)
{
DataRow dr1 = tb1.Rows[0];
empM.APPSTATES = dr1["APPSTATES"].ToString();
empM.APPNO = dr1["APPNO"].ToString();
empM.Checkwait = dr1["Checkwait"].ToString();
empM.ApplicantNo = dr1["ApplicantNo"].ToString();
empM.ApplicantName = dr1["ApplicantName"].ToString();
empM.ApplicantCode = dr1["ApplicantCode"].ToString();
empM.ApplicantMail = dr1["ApplicantMail"].ToString();
empM.ApplicantPhone = dr1["ApplicantPhone"].ToString();
empM.ApplicantDep = dr1["ApplicantDep"].ToString();
empM.Recipientunit = dr1["Recipientunit"].ToString();
empM.Organizer = dr1["Organizer"].ToString();
empM.Copysubmission = dr1["Copysubmission"].ToString();
empM.Page = dr1["Page"].ToString();
empM.Issuer = dr1["Issuer"].ToString();
empM.Documentnumber = dr1["Documentnumber"].ToString();
empM.Daycreate = dr1["Daycreate"].ToString();
empM.Title = dr1["Title"].ToString();
empM.AppContent = dr1["AppContent"].ToString();
empM.FileName = dr1["FileName"].ToString();
empM.Signer3No = dr1["Signer3No"].ToString();
empM.Signer3Name = dr1["Signer3Name"].ToString();
empM.Signer4No = dr1["Signer4No"].ToString();
empM.Signer4Name = dr1["Signer4Name"].ToString();
empM.Signer5No = dr1["Signer5No"].ToString();
empM.Signer5Name = dr1["Signer5Name"].ToString();
empM.Signer6No = dr1["Signer6No"].ToString();
empM.Signer6Name = dr1["Signer6Name"].ToString();
empM.Username = dr1["USERNAME"].ToString();
//empM.FileContent = dr1["FileContent"].ToString();
//empM.FileContent = Convert.ToByte(dr1["FileContent"].);
}
List <APPROVE_SIGN> list_A = new List <APPROVE_SIGN>();
long approveID = 0;
string appID = "";
string approverName = "";
string approverNo = "";
string processName = "";
DateTime time = DateTime.Now;
string note = "";
string status = "";
string agentIP = "";
string agent = "";
if (tb.Rows.Count > 0)
{
//DataRow dr = tb.Rows[0];
for (int i = 0; i < tb.Rows.Count; i++)
{
//approveID = Convert.ToInt32(tb.Rows[i]["APPROVEID"].ToString());
appID = tb.Rows[i]["APPNO"].ToString();
approverName = tb.Rows[i]["APPROVERNAME"].ToString();
approverNo = tb.Rows[i]["APPROVEREMP"].ToString();
processName = tb.Rows[i]["PROCESSNAME"].ToString();
note = tb.Rows[i]["NOTES"].ToString();
status = tb.Rows[i]["STATUS"].ToString();
time = Convert.ToDateTime(tb.Rows[i]["TIMEAPPROVE"].ToString());
//agentIP = tb.Rows[i]["AGENTEMP"].ToString();
//agent = tb.Rows[i]["AGENTNAME"].ToString();
list_A.Add(new APPROVE_SIGN
{
APPROVEID = approveID,
APPNO = appID,
APPROVERNAME = approverName,
APPROVEREMP = approverNo,
PROCESSNAME = processName,
NOTES = note,
STATUS = status,
TIMEAPPROVE = time
//AGENTEMP = agentIP,
//AGENTNAME = agent
});
}
}
aprApp.APPROVEs = list_A;
aprApp.EmpModels = empM;
return(aprApp);
}
/// <summary>
/// DML的操作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreateDML_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string source_DBName_f = cbxSourceDataBases.Text.Trim();//下拉列表选择的是数据库
string source_DBName = SQLServerDBHelper.IsLinkServer(source_DBName_f);
string dest_DBName_f;
string dest_DBName;
if (dest_DBName_Top == "NotNull")
{
dest_DBName_f = cbxTargetDataBases.Text.Trim();//下拉列表选择的是数据库
dest_DBName = SQLServerDBHelper.IsLinkServer(dest_DBName_f);
}
else
{
dest_DBName_f = "空数据库";
dest_DBName = "";
}
string generateScriptToStore_InsertData = "[SP_Dey_InsertDataScriptForTable]";
string operation_Type = "导入系统数据";
string fp = "d:\\SQL\\" + DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月";
string str_Type = "D";
string str_FileName = "InsertData";
string select_Result = "";
bool flog = false;
int select_Count = 0;
Directory.CreateDirectory(fp);
DirectoryInfo dir = new DirectoryInfo(fp);
if (!File.Exists(fp))
{
dir.Create();
}
if (labSource.Text == "连接结果")//判断源数据库是否进行测试连通
{
txbResult.AppendText("数据库“" + cbxSourceDataBases.Text.Trim() + "”未进行连通测试\r\n\r\n");
MessageBox.Show("请对“" + cbxSourceDataBases.Text.Trim() + "”进行连通测试");
}
else if (labTarget.Text == "连接结果" && dest_DBName_Top == "NotNull")//判断目标数据库是否进行测试连通
{
txbResult.AppendText("数据库“" + cbxTargetDataBases.Text.Trim() + "”未进行连通测试\r\n\r\n");
MessageBox.Show("请对“" + cbxTargetDataBases.Text.Trim() + "”进行连通测试");
}
else if (labSource.Text == "连接失败")//源数据库连接失败，提示重新选择数据库
{
txbResult.AppendText("源数据库无法连通，请选择正确的源数据库，并进行测试\r\n\r\n");
MessageBox.Show("源数据库无法连通，请选择正确的源数据库，并进行测试");
}
else if (labTarget.Text == "连接失败" && dest_DBName_Top == "NotNull")//目标数据库连接失败，提示重新选择数据库
{
txbResult.AppendText("目标数据库无法连通，请选择正确的目标数据库，并进行测试\r\n\r\n");
MessageBox.Show("目标数据库无法连通，请选择正确的目标数据库，并进行测试");
}
else
{
if (SQLServerDBHelper.TestResult(cbxSourceDataBases.Text.Trim()) == false)//源数据库选择之后，没有进行测试，并且有不能连通，提示进行连接测试
{
txbResult.AppendText("请点击“测试连接”按钮，确认源数据库" + cbxSourceDataBases.Text.Trim() + "是否能连通\r\n\r\n");
MessageBox.Show("请点击“测试连接”按钮，确认源数据库" + cbxSourceDataBases.Text.Trim() + "是否能连通");
}
else if (SQLServerDBHelper.TestResult(cbxTargetDataBases.Text.Trim()) == false && dest_DBName_Top == "NotNull")//目标数据库选择之后，没有进行测试，并且有不能连通，提示进行连接测试
{
txbResult.AppendText("请点击“测试连接”按钮，确认源数据库" + cbxTargetDataBases.Text.Trim() + "是否能连通\r\n\r\n");
MessageBox.Show("请点击“测试连接”按钮，确认源数据库" + cbxTargetDataBases.Text.Trim() + "是否能连通");
}
else
{
flog = true;
}
}
if (flog == true)
{
string tableName = "";
for (int t = 0; t < dgv_DataBaseName.Rows.Count; t++)
{
//int count = 0;
//if (dgv_DataBaseName.Rows[t].Cells[0].EditedFormattedValue.ToString() == "true")
//{ count++; }
//if (count == 0)
//{
// MessageBox.Show("请至少选择一条数据！", "提示");
// return;
//}
//else
//{
// string tableName = dgv_DataBaseName.Rows[t].Cells["TableName"].Value.ToString().Trim();
// SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, tableName, generateScriptToStore_InsertData); //生成创建元数据的脚本
// txbResult.AppendText("导入系统数据：“" + source_DBName_f + "”中" + tableName + "表的脚本操作成功\r\n\r\n");
//}
DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)dgv_DataBaseName.Rows[t].Cells[0];
bool flag = Convert.ToBoolean(checkCell.Value);
if (flag == true)
{
tableName += "'" + dgv_DataBaseName.Rows[t].Cells["TableName"].Value.ToString().Trim() + "',";
txbResult.AppendText("导入系统数据：“" + source_DBName_f + "”中" + tableName + "表的脚本获取成功\r\n\r\n");
select_Count++;
}
}
tableName = tableName.Remove(tableName.Length - 1, 1);
SQLServerDBHelper.GetDBInfoToStore(source_DBName, dest_DBName, tableName, generateScriptToStore_InsertData); //生成创建元数据的脚本
txbResult.AppendText("导入系统数据：“" + source_DBName_f + "”中" + tableName + "表的脚本操作成功\r\n\r\n");
if (select_Count != 0)
{
dt.Clear();
dt = SQLServerDBHelper.GetInfoStoreFile(str_Type);
txbResult.AppendText("" + operation_Type + "：获取数据库中的数据成功\r\n\r\n");
for (int i = 0; i < dt.Rows.Count; i++)//取出的数据连成字符串
{
select_Result += dt.Rows[i][0].ToString();
}
txbResult.AppendText("" + operation_Type + "：获取的数据转换成功\r\n\r\n");
string dateTime = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string filePath = fp + @"\" + str_FileName + "_" + source_DBName_f + "_" + dateTime + ".sql";//定义文件名
LinkLabel link = new LinkLabel();
link.Text = filePath;
link.Cursor = Cursors.Hand;
link.LinkBehavior = LinkBehavior.HoverUnderline;
link.Parent = this.txbResult;
this.txbResult.Controls.Add(link);
txbResult.AppendText("" + operation_Type + "：文件存储的完整路径：" + filePath + "\r\n\r\n");
FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write, FileShare.Read);
StreamWriter sw = new StreamWriter(fs);//写入文件中
fs.SetLength(0);
sw.WriteLine(select_Result);
sw.Close();
txbResult.AppendText("" + operation_Type + "：数据存储完成\r\n\r\n");
}
else
{
MessageBox.Show("至少选择一个表");
}
}
}