递归导入access数据程序|自动匹配企业分类
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Data.SqlClient;
namespace DataImportFromAccess
{
public partial class DataImport : Projects.Utils.ManageForm
{
protected ArrayList al = new ArrayList();
protected StringBuilder sbSql = new StringBuilder();
protected int i = 0;
protected int k = 0;
public DataImport()
{
InitializeComponent();
}
public void InItData()
{
}
public void Dirs(string path)
{
DirectoryInfo dis = new DirectoryInfo(path);
FileInfo[] files = dis.GetFiles();
foreach (FileInfo fi in files)
{
i++;
Application.DoEvents();
this.label1.Text = i.ToString();
try
{
if (fi.FullName.ToString().ToLower().Contains("mdb"))
{
al.Add(fi.FullName.ToString());
}
else
{
}
// File.Delete(fi.FullName);
}
catch (Exception ex)
{
//this.richTextBox1.Text += ex.Message;.
MessageBox.Show(ex.Message);
}
}
if (dis.GetDirectories().Length > 0)
{
for (int i = 0; i < dis.GetDirectories().Length; i++)
{
Dirs(dis.GetDirectories()[i].FullName);
}
}
}
private void BtSelectFile_Click(object sender, EventArgs e)
{
//this.OpdFiles.ShowDialog();
this.FbdFiles.ShowDialog();
//this.OpdFiles.
}
private void BtImport_Click(object sender, EventArgs e)
{
Dirs(this.FbdFiles.SelectedPath);
ArrayList alsql = new ArrayList();
string tempCompanyName = "";//公司名称
string tempCode = "";//邮 编
string tempAdd = "";//地 址
string tempLinkMan = "";//联 系 人
string tempTel = "";//联系电话
string tempMobile = "";//手 机
string tempFax = ""; //传 真
string tempSql = "";
string Gid = "";
string C_ID = "";
string userName = "";
string 公司名称 = "";
string 基本信息 = "";
string 主营产品或服务 = "";
string 主营行业 = "";
string 企业类型 = "";
string 经营模式 = "";
string 法人代表负责人 = "";
string 公司注册地 = "";
string 注册资金 = "";
string 员工人数 = "";
string 公司成立时间 = "";
string 年营业额 = "";
string 主要经营地点 = "";
string 主要市场 = "";
string 经营品牌 = "";
string 主要客户 = "";
string 管理体系认证 = "";
string 开户银行 = "";
string 银行帐号 = "";
string 是否提供OEM代加工 = "";
string 研发部门人数 = "";
string 厂房面积 = "";
string 质量控制 = "";
string 月产量 = "";
string 联系人 = "";
string 性别 = "";
string 部门 = "";
string 电话 = "";
string 移动电话 = "";
string 传真 = "";
string 地址 = "";
string 邮编 = "";
string 邮箱 = "";
string 公司主页 = "";
string 信用等级参考 = "";
string 年进口额 = "";
string 年出口额 = "";
string 诚信 = "";
string Fid = "";
string Sid = "";
string Tid = "";
string Province = "";
string City = "";
string Area = "";
/*
公司名称
公司简介
主营产品或服务
主营行业:
企业类型:
经营模式
法人代表
注册地点
注册资金
成立时间
年营业额
主要市场
主要客户群
管理体系认证
地址
研发人数
OEM服务
厂房面积
质量控制
月产量
联系人
电话
移动电话
传真
邮编
邮箱
网址
年出口额
年进口额
*/
int l = 0;
foreach (string str in al)
{
try
{
//this.richTextBox1.Text+=l.ToString();
DataTable dt = this.Db.GetTable(string.Format("select replace(replace(replace(cast(公司名称 as varchar(600)),char(10),''),char(13),''),char(9),'') 公司名称, replace(replace(replace(cast(公司简介 as varchar(600)),char(10),''),char(13),''),char(9),'') 公司简介,replace(replace(replace(cast(主营产品或服务 as varchar(600)),char(10),''),char(13),''),char(9),'') 主营产品或服务,[主营行业:],[企业类型:],replace(replace(replace(cast(经营模式 as varchar(600)),char(10),''),char(13),''),char(9),'') 经营模式,法人代表, 注册地点,注册资金,成立时间,年营业额,主要市场,主要客户群,管理体系认证,地址,研发人数,case cast(OEM服务 as varchar(80)) when '提供' then '1' else '0' end as OEM服务 , 厂房面积,质量控制,月产量,联系人,电话,移动电话,传真,邮编,邮箱,网址,年出口额,年进口额 from openrowset('Microsoft.Jet.OLEDB.4.0','{0}';'admin';'',Content)", str));
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
Gid = "";
C_ID = "";
k++;
int model = 500000;
userName = (model + k).ToString();
公司名称 = Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ", "");
基本信息 = Convert.ToString(dr["公司简介"]).Replace("'", "''").Replace(" ", "");
主营产品或服务 = Convert.ToString(dr["主营产品或服务"]).Replace("'", "''").Replace(" ", "");
主营行业 = Convert.ToString(dr["主营行业:"]).Replace("'", "''").Replace(" ", "");
企业类型 = Convert.ToString(dr["企业类型:"]).Replace("'", "''").Replace(" ", "");
经营模式 = Convert.ToString(dr["经营模式"]).Replace("'", "''").Replace(" ", "");
法人代表负责人 = Convert.ToString(dr["法人代表"]).Replace("'", "''").Replace(" ", "");
公司注册地 = Convert.ToString(dr["注册地点"]).Replace("'", "''").Replace(" ", "");
注册资金 = Convert.ToString(dr["注册资金"]).Replace("'", "''").Replace(" ", "");
员工人数 = "";// Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
公司成立时间 = Convert.ToString(dr["成立时间"]).Replace("'", "''").Replace(" ", "");
年营业额 = Convert.ToString(dr["年营业额"]).Replace("'", "''").Replace(" ", "");
主要经营地点 = ""; //Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
主要市场 = Convert.ToString(dr["主要市场"]).Replace("'", "''").Replace(" ", "");
经营品牌 = "";//Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
主要客户 = Convert.ToString(dr["主要客户群"]).Replace("'", "''").Replace(" ", "");
管理体系认证 = Convert.ToString(dr["管理体系认证"]).Replace("'", "''").Replace(" ", "");
开户银行 = "";//Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
银行帐号 = "";// Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
是否提供OEM代加工 = Convert.ToString(dr["OEM服务"]).Replace("'", "''").Replace(" ", "");
研发部门人数 = Convert.ToString(dr["研发人数"]).Replace("'", "''").Replace(" ", "");
厂房面积 = Convert.ToString(dr["厂房面积"]).Replace("'", "''").Replace(" ", "");
质量控制 = Convert.ToString(dr["质量控制"]).Replace("'", "''").Replace(" ", "");
月产量 = Convert.ToString(dr["月产量"]).Replace("'", "''").Replace(" ", "");
联系人 = Convert.ToString(dr["联系人"]).Replace("'", "''").Replace(" ", "");
性别 = Convert.ToString(dr["联系人"]).Replace("'", "''").Replace(" ", "").Contains("女士") ? ("女") : ("男");
部门 = "销售部";
电话 = Convert.ToString(dr["电话"]).Replace("'", "''").Replace(" ", "");
移动电话 = Convert.ToString(dr["移动电话"]).Replace("'", "''").Replace(" ", "");
传真 = Convert.ToString(dr["传真"]).Replace("'", "''").Replace(" ", "");
地址 = Convert.ToString(dr["地址"]).Replace("'", "''").Replace(" ", "");
邮编 = Convert.ToString(dr["邮编"]).Replace("'", "''").Replace(" ", "");
邮箱 = Convert.ToString(dr["邮箱"]).Replace("'", "''").Replace(" ", "");
公司主页 = Convert.ToString(dr["网址"]).Replace("'", "''").Replace(" ", "");
// 信用等级参考 = Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
年进口额 = Convert.ToString(dr["年出口额"]).Replace("'", "''").Replace(" ", "");
年出口额 = Convert.ToString(dr["年进口额"]).Replace("'", "''").Replace(" ", "");
诚信 = "";
Fid = "";
Sid = "";
Tid = "";
Province = "";
City = "";
Area = "";
tempSql = string.Format("insert into companys(C_ID ,userName ,公司名称,基本信息 ,主营产品或服务,主营行业,企业类型,经营模式,法人代表负责人,公司注册地,注册资金,员工人数,公司成立时间,年营业额,主要经营地点,主要市场,经营品牌,主要客户,管理体系认证,开户银行,银行帐号,是否提供OEM代加工,研发部门人数,厂房面积,质量控制,月产量,联系人,性别,部门,电话,移动电话,传真,地址,邮编,邮箱,公司主页,年进口额,年出口额) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}')", C_ID, userName, 公司名称, 基本信息, 主营产品或服务, 主营行业, 企业类型, 经营模式, 法人代表负责人, 公司注册地, 注册资金, 员工人数, 公司成立时间, 年营业额, 主要经营地点, 主要市场, 经营品牌, 主要客户, 管理体系认证, 开户银行, 银行帐号, 是否提供OEM代加工, 研发部门人数, 厂房面积, 质量控制, 月产量, 联系人, 性别, 部门, 电话, 移动电话, 传真, 地址, 邮编, 邮箱, 公司主页, 年进口额, 年出口额);
sbSql.Append(tempSql).Append("\r\n");
}
WriteSqls(sbSql.ToString());
Application.DoEvents();
//this.richTextBox1.Text += sbSql.ToString() + "\r\n";
sbSql.Remove(0, sbSql.Length);
}
}
catch (Exception ex)
{
continue;
MessageBox.Show(ex.Message);
}
}
MessageBox.Show("success");
}
public void WriteSqls(params string[] sql)
{
if (sql.Length < 2)
{
using (StreamWriter sw = new StreamWriter("D:\\sql.txt", true, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.Write(sql[0]);
sw.Flush();
}
}
else if (sql[1] == "pro")
{
using (StreamWriter sw = new StreamWriter("D:\\sqlContent.txt", true, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.Write(sql[0]);
sw.Flush();
}
}
else if (sql[1] == "updateCity")
{
using (StreamWriter sw = new StreamWriter("D:\\sqlupdateCity.txt", true, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.Write(sql[0]);
sw.Flush();
}
}
else
{
using (StreamWriter sw = new StreamWriter("D:\\sql" + sql[1] + ".txt", true, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.Write(sql[0]);
sw.Flush();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
Dirs(this.FbdFiles.SelectedPath);
ArrayList alsql = new ArrayList();
string tempCompanyName = "";//公司名称
string tempCode = "";//邮 编
string tempAdd = "";//地 址
string tempLinkMan = "";//联 系 人
string tempTel = "";//联系电话
string tempMobile = "";//手 机
string tempFax = ""; //传 真
string tempSql = "";
//,标题,产品品牌,产品图,单价,发布时间,公司名称,供货总量,计量单位,缩略图,所在地
string 标题 = "";
string 产品描述 = "";
string 产品品牌 = "";
string 产品图 = "";
string 单价 = "";
string 发布时间 = "";
string 公司名称 = "";
string 供货总量 = "";
string 计量单位 = "";
string 所在地 = "";
/*
string 法人代表负责人 = "";
string 公司注册地 = "";
string 注册资金 = "";
string 员工人数 = "";
string 公司成立时间 = "";
string 年营业额 = "";
string 主要经营地点 = "";
string 主要市场 = "";
string 经营品牌 = "";
string 主要客户 = "";
string 管理体系认证 = "";
string 开户银行 = "";
string 银行帐号 = "";
string 是否提供OEM代加工 = "";
string 研发部门人数 = "";
string 厂房面积 = "";
string 质量控制 = "";
string 月产量 = "";
string 联系人 = "";
string 性别 = "";
string 部门 = "";
string 电话 = "";
string 移动电话 = "";
string 传真 = "";
string 地址 = "";
string 邮编 = "";
string 邮箱 = "";
string 公司主页 = "";
string 信用等级参考 = "";
string 年进口额 = "";
string 年出口额 = "";
string 诚信 = "";
string Fid = "";
string Sid = "";
string Tid = "";
string Province = "";
string City = "";
string Area = "";*/
/*
公司名称
公司简介
主营产品或服务
主营行业:
企业类型:
经营模式
法人代表
注册地点
注册资金
成立时间
年营业额
主要市场
主要客户群
管理体系认证
地址
研发人数
OEM服务
厂房面积
质量控制
月产量
联系人
电话
移动电话
传真
邮编
邮箱
网址
年出口额
年进口额
*/
//,标题,产品品牌,产品图,单价,发布时间,公司名称,供货总量,计量单位,缩略图,所在地
// 公司名称,产品名,数量,规格,品牌,产地,单位,详细信息,产品图
int l = 0;
foreach (string str in al)
{
try
{
//this.richTextBox1.Text+=l.ToString();
DataTable dt = this.Db.GetTable(string.Format("select 公司名称,产品名,replace(replace(replace(cast(详细信息 as varchar(600)),char(10),''),char(13),''),char(9),'') 详细信息,replace(replace(replace(cast(单价 as varchar(600)),char(10),''),char(13),''),char(9),'') 单价,replace(replace(replace(cast(数量 as varchar(600)),char(10),''),char(13),''),char(9),'') 数量,replace(replace(replace(cast(规格 as varchar(600)),char(10),''),char(13),''),char(9),'') 规格,replace(replace(replace(cast(品牌 as varchar(600)),char(10),''),char(13),''),char(9),'') as 品牌,replace(replace(replace(cast(产地 as varchar(600)),char(10),''),char(13),''),char(9),'') 产地,replace(replace(replace(cast(单位 as varchar(600)),char(10),''),char(13),''),char(9),'') 单位,replace(replace(replace(cast(产品图 as varchar(600)),char(10),''),char(13),''),char(9),'') 产品图 from openrowset('Microsoft.Jet.OLEDB.4.0','{0}';'admin';'',Content)", str));
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//Gid = "";
// C_ID = "";
k++;
int model = 500000;
//userName = (model + k).ToString();
标题 = Convert.ToString(dr["产品名"]).Replace("'", "''").Replace(" ", "");
产品描述 = Convert.ToString(dr["详细信息"]).Replace("'", "''").Replace(" ", "");
产品品牌 = Convert.ToString(dr["品牌"]).Replace("'", "''").Replace(" ", "");
产品图 = Convert.ToString(dr["产品图"]).Replace("'", "''").Replace(" ", "");
单价 = Convert.ToString(dr["单价"]).Replace("'", "''").Replace(" ", "");
发布时间 = "";// Convert.ToString(dr["企业类型:"]).Replace("'", "''").Replace(" ", "");
公司名称 = Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ", "");
供货总量 = Convert.ToString(dr["数量"]).Replace("'", "''").Replace(" ", "");
计量单位 = Convert.ToString(dr["单位"]).Replace("'", "''").Replace(" ", "");
所在地 = Convert.ToString(dr["产地"]).Replace("'", "''").Replace(" ", "");
//insert into ProContent(标题,产品品牌,产品图,单价,公司名称,供货总量,计量单位,所在地) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')
tempSql = string.Format("insert into ProContent(标题,产品描述,产品品牌,产品图,单价,公司名称,供货总量,计量单位,所在地) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", 标题, 产品描述, 产品品牌, 产品图, 单价, 公司名称, 供货总量, 计量单位, 所在地);
//alsql.Add(tempSql);
sbSql.Append(tempSql).Append("\r\n");
/* 公司成立时间 = Convert.ToString(dr["成立时间"]).Replace("'", "''").Replace(" ", "");
年营业额 = Convert.ToString(dr["年营业额"]).Replace("'", "''").Replace(" ", "");
主要经营地点 = ""; //Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
主要市场 = Convert.ToString(dr["主要市场"]).Replace("'", "''").Replace(" ", "");
经营品牌 = "";//Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
主要客户 = Convert.ToString(dr["主要客户群"]).Replace("'", "''").Replace(" ", "");
管理体系认证 = Convert.ToString(dr["管理体系认证"]).Replace("'", "''").Replace(" ", "");
开户银行 = "";//Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
银行帐号 = "";// Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
是否提供OEM代加工 = Convert.ToString(dr["OEM服务"]).Replace("'", "''").Replace(" ", "");
研发部门人数 = Convert.ToString(dr["研发人数"]).Replace("'", "''").Replace(" ", "");
厂房面积 = Convert.ToString(dr["厂房面积"]).Replace("'", "''").Replace(" ", "");
质量控制 = Convert.ToString(dr["质量控制"]).Replace("'", "''").Replace(" ", "");
月产量 = Convert.ToString(dr["月产量"]).Replace("'", "''").Replace(" ", "");
联系人 = Convert.ToString(dr["联系人"]).Replace("'", "''").Replace(" ", "");
性别 = Convert.ToString(dr["联系人"]).Replace("'", "''").Replace(" ", "").Contains("女士") ? ("女") : ("男");
部门 = "销售部";
电话 = Convert.ToString(dr["电话"]).Replace("'", "''").Replace(" ", "");
移动电话 = Convert.ToString(dr["移动电话"]).Replace("'", "''").Replace(" ", "");
传真 = Convert.ToString(dr["传真"]).Replace("'", "''").Replace(" ", "");
地址 = Convert.ToString(dr["地址"]).Replace("'", "''").Replace(" ", "");
邮编 = Convert.ToString(dr["邮编"]).Replace("'", "''").Replace(" ", "");
邮箱 = Convert.ToString(dr["邮箱"]).Replace("'", "''").Replace(" ", "");
公司主页 = Convert.ToString(dr["网址"]).Replace("'", "''").Replace(" ", "");
// 信用等级参考 = Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
年进口额 = Convert.ToString(dr["年出口额"]).Replace("'", "''").Replace(" ", "");
年出口额 = Convert.ToString(dr["年进口额"]).Replace("'", "''").Replace(" ", "");
诚信 = "";
Fid = "";
Sid = "";
Tid = "";
Province = "";
City = "";
Area = "";
//tempCompanyName = Convert.ToString(dr["公司名称"]).Replace("'", "''").Replace(" ","");
//tempAdd = Convert.ToString(dr["地址"]).Replace("'", "''");
//tempLinkMan = Convert.ToString(dr["联系人"]).Replace("'", "''");
//tempTel = Convert.ToString(dr["电话"]).Replace("'", "''");
//tempMobile = Convert.ToString(dr["移动电话"]).Replace("'", "''");
//tempFax = Convert.ToString(dr["传真"]).Replace("'", "''");*/
}
WriteSqls(sbSql.ToString(), "");
Application.DoEvents();
//this.richTextBox1.Text += sbSql.ToString() + "\r\n";
sbSql.Remove(0, sbSql.Length);
}
}
catch (Exception ex)
{
continue;
MessageBox.Show(ex.Message);
}
}
MessageBox.Show("success");
}
private void DataImport_Load(object sender, EventArgs e)
{
}
private void richTextBox1_TextChanged(object sender, EventArgs e)
{
}
private void btCompanys_Click(object sender, EventArgs e)
{
try
{
SqlDataReader drd = null;
DataTable dtProvince = this.Db.GetTable("select province from province");
DataTable dtCity = this.Db.GetTable("select city,province from city b,province c where b.father=c.provinceid order by province,city");
DataTable dtArea = this.Db.GetTable("select area,city,province from area a,city b,province c where a.father=b.cityid and b.father=c.provinceid order by province,city");
string Columns = this.Tbcolumn.Text.Trim();
string[] tempColumns = Columns.Split(',');
string tempColumn = "";
string tempArea = "";
string tempCity = "";
string tempProvince = "";
int tempCount = 0;
StringBuilder tempSql = new StringBuilder("");
DataRow dr = null;
for (int i = 0; i < tempColumns.Length; i++)
{
//drd.Dispose();
//drd = null;
// MessageBox.Show(Columns + "select Gid,userName," + tempColumns[0] + "-" + tempColumns[1] + " from companys");
drd = this.Db.GetDataReader("select Gid,userName,[" + tempColumns[i] + "] from companys");
// MessageBox.Show(tempColumns[i] + drd.Read().ToString());
while (drd.Read())
{
bool tempState = false;
//MessageBox.Show(Columns + "--" + tempColumn + "-");
//if (i == 0) { drd.Close(); break; }
tempColumn = Convert.ToString(drd[tempColumns[i]]);
for (int j = 0; j < dtArea.Rows.Count; j++)
{
dr = dtArea.Rows[j];
tempArea = Convert.ToString(dr["area"]);//.Replace("区", "").Replace("县", "");
if (tempColumn.Contains(tempArea))
{
sbSql.AppendFormat("update companys set Province='{0}',city='{1}',area='{2}' where Gid={3} and area is null;\r\n", Convert.ToString(dr["Province"]), Convert.ToString(dr["City"]), Convert.ToString(dr["Area"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
}
}
if (!tempState)
{
for (int j = 0; j < dtCity.Rows.Count; j++)
{
dr = dtCity.Rows[j];
tempArea = Convert.ToString(dr["city"]).Replace("市", "");
if (tempColumn.Contains(tempArea))
{
sbSql.AppendFormat("update companys set Province='{0}',city='{1}',area='市辖区' where Gid={2} and area is null;\r\n", Convert.ToString(dr["Province"]), Convert.ToString(dr["City"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
}
}
}
if (!tempState)
{
for (int j = 0; j < dtProvince.Rows.Count; j++)
{
dr = dtProvince.Rows[j];
tempArea = Convert.ToString(dr["Province"]).Replace("省", "");
if (tempColumn.Contains(tempArea))
{
sbSql.AppendFormat("update companys set Province='{0}',city='市辖区',area='市辖区' where Gid={1} and area is null;\r\n", Convert.ToString(dr["Province"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
}
}
}
if (!tempState)
{
for (int j = 0; j < dtArea.Rows.Count; j++)
{
dr = dtArea.Rows[j];
tempArea = Convert.ToString(dr["area"]).Replace("镇", "").Replace("区", "").Replace("县", "");
if (tempColumn.Contains(tempArea))
{
sbSql.AppendFormat("update companys set Province='{0}',city='{1}',area='{2}' where Gid={3} and area is null;\r\n", Convert.ToString(dr["Province"]), Convert.ToString(dr["City"]), Convert.ToString(dr["Area"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
}
}
}
if (tempCount % 20000 == 0)
{
WriteSqls(sbSql.ToString(), "updateCity");
sbSql.Remove(0, sbSql.Length);
}
this.label2.Text = tempCount.ToString();
Application.DoEvents();
}
drd.Close();
//
}
WriteSqls(sbSql.ToString(), "updateCity");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btCompanySort_Click(object sender, EventArgs e)
{
try
{
SqlDataReader drd = null;
DataTable dtProvince = this.Db.GetTable("select Column_Name,Column_ID from Sort where Column_depth=1");
DataTable dtCity = this.Db.GetTable("select b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort b,Sort c where b.Parent_ID=c.Column_ID and b.Column_Depth=2 order by FName,SName");
DataTable dtArea = this.Db.GetTable("select a.Column_Name TName,a.Column_ID Tid,b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort a,Sort b,Sort c where a.Parent_ID=b.Column_ID and b.Parent_ID=c.Column_ID order by FName,SName");
string Columns = this.tbCompanySort.Text.Trim();
string[] tempColumns = Columns.Split(',');
string tempColumn = "";
string tempArea = "";
string tempCity = "";
string tempProvince = "";
int tempCount = 0;
StringBuilder tempSql = new StringBuilder("");
DataRow dr = null;
for (int i = 0; i < tempColumns.Length; i++)
{
//drd.Dispose();
//drd = null;
// MessageBox.Show(Columns + "select Gid,userName," + tempColumns[0] + "-" + tempColumns[1] + " from companys");
drd = this.Db.GetDataReader("select Gid,userName,[" + tempColumns[i] + "] from companys");
// MessageBox.Show(tempColumns[i] + drd.Read().ToString());
while (drd.Read())
{
bool tempState = false;
//MessageBox.Show(Columns + "--" + tempColumn + "-");
//if (i == 0) { drd.Close(); break; }
tempColumn = Convert.ToString(drd[tempColumns[i]]);
for (int j = 0; j < dtArea.Rows.Count; j++)
{
dr = dtArea.Rows[j];
tempArea = Convert.ToString(dr["TName"]);//.Replace("区", "").Replace("县", "");
if (tempColumn.Contains(tempArea) && tempArea!="其他")
{
sbSql.AppendFormat("update companys set Fid='{0}',Sid='{1}',Tid='{2}' where Gid={3} and Fid is null;\r\n", Convert.ToString(dr["Fid"]), Convert.ToString(dr["Sid"]), Convert.ToString(dr["Tid"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
if (!tempState)
{
for (int j = 0; j < dtCity.Rows.Count; j++)
{
dr = dtCity.Rows[j];
tempArea = Convert.ToString(dr["SName"]);//.Replace("市", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update companys set Fid='{0}',Sid='{1}',Tid='' where Gid={2} and Fid is null;\r\n", Convert.ToString(dr["Fid"]), Convert.ToString(dr["Sid"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
}
if (!tempState)
{
for (int j = 0; j < dtProvince.Rows.Count; j++)
{
dr = dtProvince.Rows[j];
tempArea = Convert.ToString(dr["Column_Name"]);//.Replace("省", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update companys set Fid='{0}',Sid='',Tid='' where Gid={1} and Fid is null;\r\n", Convert.ToString(dr["Column_ID"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
}
if (tempCount % 20000 == 0)
{
WriteSqls(sbSql.ToString(), "updateSort");
sbSql.Remove(0, sbSql.Length);
}
this.label2.Text = tempCount.ToString();
Application.DoEvents();
}
drd.Close();
//
}
WriteSqls(sbSql.ToString(), "updateSort");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
MessageBox.Show("success!");
}
private void BtProductSort_Click(object sender, EventArgs e)
{
try
{
SqlDataReader drd = null;
DataTable dtProvince = this.Db.GetTable("select Column_Name,Column_ID from Sort where Column_depth=1");
DataTable dtCity = this.Db.GetTable("select b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort b,Sort c where b.Parent_ID=c.Column_ID and b.Column_Depth=2 order by FName,SName");
DataTable dtArea = this.Db.GetTable("select a.Column_Name TName,a.Column_ID Tid,b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort a,Sort b,Sort c where a.Parent_ID=b.Column_ID and b.Parent_ID=c.Column_ID order by FName,SName");
string Columns = this.tbProductSort.Text.Trim();
string[] tempColumns = Columns.Split(',');
string tempColumn = "";
string tempArea = "";
string tempCity = "";
string tempProvince = "";
int tempCount = 0;
StringBuilder tempSql = new StringBuilder("");
DataRow dr = null;
for (int i = 0; i < tempColumns.Length; i++)
{
//drd.Dispose();
//drd = null;
// MessageBox.Show(Columns + "select Gid,userName," + tempColumns[0] + "-" + tempColumns[1] + " from companys");
drd = this.Db.GetDataReader("select Gid,[" + tempColumns[i] + "] from ProContent");
// MessageBox.Show(tempColumns[i] + drd.Read().ToString());
while (drd.Read())
{
bool tempState = false;
tempColumn = Convert.ToString(drd[tempColumns[i]]);
for (int j = 0; j < dtArea.Rows.Count; j++)
{
dr = dtArea.Rows[j];
tempArea = Convert.ToString(dr["TName"]);//.Replace("区", "").Replace("县", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update ProContent set Fid='{0}',Sid='{1}',Tid='{2}' where Gid={3} and Fid is null;\r\n", Convert.ToString(dr["Fid"]), Convert.ToString(dr["Sid"]), Convert.ToString(dr["Tid"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
if (!tempState)
{
for (int j = 0; j < dtCity.Rows.Count; j++)
{
dr = dtCity.Rows[j];
tempArea = Convert.ToString(dr["SName"]);//.Replace("市", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update ProContent set Fid='{0}',Sid='{1}',Tid='' where Gid={2} and Fid is null;\r\n", Convert.ToString(dr["Fid"]), Convert.ToString(dr["Sid"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
}
if (!tempState)
{
for (int j = 0; j < dtProvince.Rows.Count; j++)
{
dr = dtProvince.Rows[j];
tempArea = Convert.ToString(dr["Column_Name"]);//.Replace("省", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update ProContent set Fid='{0}',Sid='',Tid='' where Gid={1} and Fid is null;\r\n", Convert.ToString(dr["Column_ID"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
}
if (tempCount % 20000 == 0)
{
WriteSqls(sbSql.ToString(), "updateProContentSort");
sbSql.Remove(0, sbSql.Length);
}
this.label2.Text = tempCount.ToString();
Application.DoEvents();
}
drd.Close();
//
}
WriteSqls(sbSql.ToString(), "updateProContentSort");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
MessageBox.Show("success!");
}
public string replaceCharacter(string str)
{
string[] tempStr = new string[] { "经营部", "贸易部", "采购部", "销售中心", "办事处", "有限公司", "公司","代表处","经营部","采购中心","上海分公司","宁波分公司","上海办事处","营销中心","浙江办事处","宁波分公司","（销售部）","（业务部）","（经销商）" };
foreach (string s in tempStr)
{
str = str.Replace(s,"");
}
return str;
}
private void btProductCompany_Click(object sender, EventArgs e)
{
///匹配产品公司
try
{
SqlDataReader drd = null;
//DataTable dtProvince = this.Db.GetTable("select C_ID,公司名称 from companys");
////DataTable dtCity = this.Db.GetTable("select b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort b,Sort c where b.Parent_ID=c.Column_ID and b.Column_Depth=2 order by FName,SName");
DataTable dtArea = this.Db.GetTable("select C_ID,公司名称 from companys");
string Columns = this.TbProductCompany.Text.Trim();
string[] tempColumns = Columns.Split(',');
string tempColumn = "";
string tempArea = "";
string tempCity = "";
string tempProvince = "";
int tempCount = 0;
StringBuilder tempSql = new StringBuilder("");
DataRow dr = null;
for (int i = 0; i < tempColumns.Length; i++)
{
//drd.Dispose();
//drd = null;
// MessageBox.Show(Columns + "select Gid,userName," + tempColumns[0] + "-" + tempColumns[1] + " from companys");
drd = this.Db.GetDataReader("select Gid,[" + tempColumns[i] + "] from ProContent");
while (drd.Read())
{
bool tempState = false;
tempColumn = Convert.ToString(drd[tempColumns[i]]);
for (int j = 0; j < dtArea.Rows.Count; j++)
{
dr = dtArea.Rows[j];
tempArea = replaceCharacter(Convert.ToString(dr["公司名称"]));//.Replace("区", "").Replace("县", "");
if (tempColumn.Contains(tempArea) && tempArea != "其他")
{
sbSql.AppendFormat("update ProContent set C_ID='{0}' where Gid={1} and C_ID is null;\r\n", Convert.ToString(dr["C_ID"]), Convert.ToString(drd["Gid"]));
tempCount++;
tempState = true;
break;
}
}
if (tempCount % 20000 == 0)
{
WriteSqls(sbSql.ToString(), "updateProContentC_ID");
sbSql.Remove(0, sbSql.Length);
}
this.label2.Text = tempCount.ToString();
Application.DoEvents();
}
drd.Close();
//
}
WriteSqls(sbSql.ToString(), "updateProContentC_ID");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
MessageBox.Show("success!");
}
private void btCompanyProduct_Click(object sender, EventArgs e)
{
//将没有分类的产品用公司的分类表示
this.Db.GetState("update ProContnt set Fid=(select Fid from companys where C_ID=ProContnt.C_ID),Sid=(select Sid from companys where C_ID=ProContnt.C_ID),Tid=(select Tid from companys where C_ID=ProContnt.C_ID) where Fid is null");
MessageBox.Show("success!");
}
}
}
/*
* select a.Column_Name TName,a.Column_ID Tid,b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort a,Sort b,Sort c where
a.Parent_ID=b.Column_ID and b.Parent_ID=c.Column_ID order by FName,SName
select b.Column_Name SName,b.Column_ID Sid,c.Column_Name FName,c.Column_ID Fid from Sort b,Sort c where
b.Parent_ID=c.Column_ID and b.Column_Depth=2 order by FName,SName
*/