//将最低生活保障数据Excel格式批量导入数据库中 满足审计部门需要
//试用请登录http://121.18.78.216/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MyQuery.Utils;
using System.IO;
using MyQuery.DAL;
namespace dbsj
{
public partial class Form1 : Form
{
string[] tables = new string[] { "SJ_DB_JTXX_河北保定市", "SJ_DB_FFMX_河北保定市" };
string[] tpaths = new string[] { "JTXX", "FFMX" };
int[] tlengths = new int[] { 22, 10 };
string[] tsqls = new string[] { @"CREATE TABLE [{0}]([XZQHDM] varchar(6), [JTBH] varchar(64), [LB] varchar(1), [XM_HZ] varchar(64), [SFZH_HZ] varchar(20), [DBZH] varchar(64), [YHZH] varchar(64), [XM_JTCY1] varchar(64), [SFZH _JTCY1] varchar(20), [XM_JTCY2] varchar(64), [SFZH _JTCY2] varchar(20), [XM_JTCY3] varchar(64), [SFZH _JTCY3] varchar(20), [XM_JTCY4] varchar(64), [SFZH _JTCY4] varchar(20), [XM_JTCY5] varchar(64), [SFZH _JTCY5] varchar(20), [XM_JTCY6] varchar(64), [SFZH _JTCY6] varchar(20), [BZRK] int, [YSR] decimal(38,6)) ON [PRIMARY]", @"CREATE TABLE [{0}]([XZQHDM] varchar(6), [JTBH] varchar(64), [XM_HZ] varchar(64), [SFZH_HZ] varchar(20), [DBZH] varchar(64), [YHZH] varchar(64), [FFSJ] varchar(6), [DBJ] decimal(38,6), [BTJE_QT] decimal(38,6)) ON [PRIMARY]" };
string[] isqls = new string[] { @"insert into [{0}] values('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}' , '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}' , '{17}' , '{18}' , '{19}' , {20} , {21} )", @"insert into [{0}] values('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, {9})" };
string[,] tfields = new string[,]{ {"XZQHDM",
"JTBH",
"LB",
"XM_HZ",
"SFZH_HZ",
"DBZH",
"YHZH",
"XM_JTCY1",
"SFZH_JTCY1",
"XM_JTCY2",
"SFZH_JTCY2",
"XM_JTCY3",
"SFZH_JTCY3",
"XM_JTCY4",
"SFZH_JTCY4",
"XM_JTCY5",
"SFZH_JTCY5",
"XM_JTCY6",
"SFZH_JTCY6",
"BZRS",
"YSR"},
{"XZQHDB",
"JTBH",
"XM_HZ",
"SFZH_HZ",
"DBZH",
"YHZH",
"FFSJ",
"DBJ",
"BTJE_QT",
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null}};
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
this.richTextBox1.Clear();
string root = WebHelper.GetRootServerPath();
if (!Directory.Exists(root + textBox2.Text))
{
MessageBox.Show(textBox2.Text + "不在程序目录中");
return;
}
for (int t = 0; t < 2; t++)
{
if (!Directory.Exists(root + textBox2.Text + "\\" + tpaths[t]))
{
MessageBox.Show(tpaths[t] + "不在" + textBox2.Text + "目录中");
}
Dao data = new Dao(DBType.Sql2005, textBox1.Text);
//创建表
string tableName = tables[t] + textBox2.Text;
if (!"1".Equals(data.GetScalar("SELECT count(*) FROM sysobjects WHERE id = OBJECT_ID(N'[" + tableName + "]') AND type in (N'U')")))
{
data.SqlExecute(String.Format(tsqls[t], tableName));
}
DirectoryInfo dInfo = new DirectoryInfo(root + textBox2.Text + "\\" + tpaths[t]);
foreach (FileInfo file in dInfo.GetFiles())
{
//插入数据
DataSet ds = ExcelHelper.GetDataFromExcel(file.FullName, true);
if (ds == null || ds.Tables.Count == 0)
{
this.richTextBox1.AppendText(Environment.NewLine + file.Name + "没有数据" + Environment.NewLine);
}
else
{
string sql = "";
try
{
data.TransactionBegin();
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
if (!DataHelper.IsNullOrEmpty(dr[0]))
{
if (t == 0)
{
if ("1".Equals(data.GetScalar("select count(*) from " + tableName + " where " + tfields[t, 1] + "='" + dr[1].ToString() + "'")))
{
//存在则跳过
continue;
}
}
object[] objs = new object[tlengths[t]];
objs[0] = tableName;
for (int j = 1; j < tlengths[t]; j++)
{
objs[j] = DataHelper.GetValue(dr, tfields[t, j - 1]);
}
for (int j = tlengths[t] - 2; j < tlengths[t]; j++)
{
if (DataHelper.IsNullOrEmpty(objs[j]))
{
objs[j] = 0;
}
}
sql = String.Format(isqls[t], objs);
data.SqlExecute(sql);
}
}
data.TransactionCommit();
}
catch (Exception ex)
{
data.TransactionRollback();
this.richTextBox1.AppendText(Environment.NewLine + file.Name + "错误:" + ex.Message + "；" + sql + Environment.NewLine);
continue;
}
file.Delete();
this.richTextBox1.AppendText(file.Name + "完成" + Environment.NewLine);
}
}
}
}
}
}