/// <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>
/// 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("至少选择一个表");
}
}
}
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);
}