internal string GenerateAID(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT MFR as MFR , AircraftModel as AMC,AircraftMFRSN as AIN From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() == "" || oleDbDataReader.GetValue(1).ToString() == "" || oleDbDataReader.GetValue(2).ToString() == "")
{
stringBuilder.ToString();
}
else
{
stringBuilder.Append("<AID_Segment>");
stringBuilder.Append("<MFR>" + oleDbDataReader.GetValue(0).ToString() + "</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("</AID_Segment>");
}
}
}
return(stringBuilder.ToString());
}
internal string GenerateSUS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblRepairHistory.DateReturned as SHD, tblRepairHistory.ShippedMFRPN as MPN, tblRepairHistory.ShippedMFRSN as SER, tblRepairHistory.ShippedPMODL as PML From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<SUS_Segment>");
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(0).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
else
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + DateTime.Now.ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
stringBuilder.Append("<MFR>10933</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
if (oleDbDataReader.GetValue(3).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(3).ToString() + ">" + oleDbDataReader.GetValue(3).ToString() + "</" + oleDbDataReader.GetName(3).ToString() + ">");
}
}
stringBuilder.Append("</SUS_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateRLS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblNewUnits.PartNumber as MPN,tblNewUnits.SerialNumber as SER,tblRepairHistory.RemovalDate as RED From tblRepairHistory, tblNewUnits WHERE tblRepairHistory.SerialNumber = tblNewUnits.SerialNumber AND RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(2).ToString() == "")
{
stringBuilder.ToString();
}
else
{
stringBuilder.Append("<RLS_Segment>");
stringBuilder.Append("<MFR>" + (object)10933 + "</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(2).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("</RLS_Segment>");
}
}
}
return(stringBuilder.ToString());
}
internal string GenerateRCS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblRepairHistory.RANumber as SFI, tblRepairHistory.DateReceived as MRD, tblNewUnits.PartNumber as MPN, tblRepairHistory.SerialNumber as SER,tblRepairHistory.SupRemType as RRC, tblRepairHistory.FFFCD as FFC, tblRepairHistory.FFICD as FFI,tblRepairHistory.FFCRRCD as FCR, tblRepairHistory.FFCAMCD as FAC, tblRepairHistory.FFCABCD as FBC, tblRepairHistory.HSFCD as FHS, tblRepairHistory.RemovedPMODL as PML From tblRepairHistory, tblNewUnits WHERE tblRepairHistory.SerialNumber = tblNewUnits.SerialNumber AND tblRepairHistory.RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<RCS_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(1).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<MFR>10933</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(3).ToString() + ">" + oleDbDataReader.GetValue(3).ToString() + "</" + oleDbDataReader.GetName(3).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(4).ToString() + ">" + oleDbDataReader.GetValue(4).ToString() + "</" + oleDbDataReader.GetName(4).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(5).ToString() + ">" + oleDbDataReader.GetValue(5).ToString() + "</" + oleDbDataReader.GetName(5).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(6).ToString() + ">" + oleDbDataReader.GetValue(6).ToString() + "</" + oleDbDataReader.GetName(6).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(7).ToString() + ">" + oleDbDataReader.GetValue(7).ToString() + "</" + oleDbDataReader.GetName(7).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(8).ToString() + ">NA</" + oleDbDataReader.GetName(8).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(9).ToString() + ">NA</" + oleDbDataReader.GetName(9).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(10).ToString() + ">" + oleDbDataReader.GetValue(10).ToString() + "</" + oleDbDataReader.GetName(10).ToString() + ">");
if (oleDbDataReader.GetValue(11).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(11).ToString() + ">" + oleDbDataReader.GetValue(11).ToString() + "</" + oleDbDataReader.GetName(11).ToString() + ">");
}
}
stringBuilder.Append("</RCS_Segment>");
}
return(stringBuilder.ToString());
}
/// <summary>
/// Inserta las cuotas correspondiente al un prestamo
/// </summary>
/// <param name="oldcodigo">codigo de prestamo del archivo acces</param>
/// <param name="newcodigo">codigo de prestamo</param>
private void InsertarCuotasPrestamo(string oldcodigo, string newcodigo)
{
string sql = "SELECT codigo_prestamo, abono, fecha FROM T_Cuotas";
sql += " WHERE codigo_prestamo = '" + oldcodigo + "'";
OleDbDataReader dr = new OleDbCommand(sql, conex).ExecuteReader();
try
{
ConexionDB con = new ConexionDB();
if (dr.HasRows)
{
while (dr.Read())
{
double abono = Convert.ToDouble(dr.GetValue(1));
string codigo = newcodigo;
string fecha = dr.GetDateTime(2).ToShortDateString();
int numero = new Cuota().MaximaCuota() + 1;
string sql2 = " INSERT INTO tcuotas(abono, codigo_prestamo, fecha, numero)";
sql2 += " VALUES (" + abono + ", '" + codigo + "', '" + fecha + "', " + numero + ");";
con.Ejecutar(sql2);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error al momento de insertar una cuota: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void BDselect(out uint[] masNul)
{
string connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + NameFileSetUp;
masNul = new uint[12];
DataAccesBDBAAK.Path = NameFileSetUp;
if (NameFileSetUp.Split('.')[1] == "db" || NameFileSetUp.Split('.')[1] == "db3")
{
List <ClassNullLine> list = DataAccesBDBAAK.GetDataNullLine();
var f = from el in DataAccesBDBAAK.GetDataNullLine() where el.namePSB == NameBAAK12 select el;
int[] mass = f.ElementAt(0).nullLine;
for (int i = 0; i < 12; i++)
{
masNul[i] = Convert.ToUInt32(mass[i]);
}
}
else
{
// Создание подключения
var podg = new OleDbConnection(connectionString);
try
{
// Открываем подключение
podg.Open();
var chit = new OleDbCommand
{
Connection = podg,
CommandText = "select * from [Нулевая линия] where ИмяПлаты ='" + NameBAAK12 + "'"
}.ExecuteReader(CommandBehavior.CloseConnection);
while (chit.Read() == true)
{
for (int i = 2; i < chit.FieldCount; i++)
{
masNul[i - 2] = Convert.ToUInt32(chit.GetValue(i));
}
}
new OleDbCommand
{
Connection = podg,
CommandText = "select * from [Нулевая линия] where Плата ='" + NameBAAK12 + "'"
}.Dispose();
}
catch (NullReferenceException ee)
{
Debug.WriteLine("Error 428");
}
catch (Exception ex)
{
MessageBox.Show("BDselect 428" + ex.Message);
}
finally
{
// закрываем подключение
podg.Close();
}
}
}
/// <summary>
/// Devuelve el indice del prestamo correpondiente
/// </summary>
/// <param name="codigo">codigo del prestamo relacionado al indice</param>
/// <returns></returns>
private int getIndiceOleDb(string codigo)
{
int indice = 0;
string sql = "SELECT num_indice FROM Indices WHERE codigo_prestamo = '" + codigo + "'";
OleDbDataReader dr = new OleDbCommand(sql, conex).ExecuteReader();
if (dr.HasRows)
{
dr.Read();
indice = Convert.ToInt32(dr.GetValue(0));
}
return(indice);
}
internal string GenerateShopFindingsDetails(string OprCode, string customer, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
if (OprCode != string.Empty)
{
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT * From tblRepairHistory WHERE OprCode = '" + OprCode + "' AND Customer = '" + customer + "'AND SerialNumber LIKE 'cj%' AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# Order by DateReceived DESC", conn).ExecuteReader();
new OleDbDataAdapter(new OleDbCommand("SELECT * From tblRepairHistory WHERE OprCode = '" + OprCode + "' AND Customer = '" + customer + "' AND SerialNumber LIKE 'cj%' AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# Order by DateReceived DESC", conn)).Fill(new DataSet());
if (oleDbDataReader.HasRows)
{
while (oleDbDataReader.Read())
{
stringBuilder.Append("<ShopFindingsDetails>");
stringBuilder.Append(this.GenerateRCS(oleDbDataReader.GetValue(0).ToString(), conn));
stringBuilder.Append(this.GenerateSAS(oleDbDataReader.GetValue(0).ToString(), conn));
stringBuilder.Append(this.GenerateRLS(oleDbDataReader.GetValue(0).ToString(), conn));
stringBuilder.Append(this.GenerateAID(oleDbDataReader.GetValue(0).ToString(), conn));
stringBuilder.Append("</ShopFindingsDetails>");
}
}
}
return(stringBuilder.ToString());
}
internal string GenerateSAS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT PartsChanged as [INT], SRLCD as SHL, SFAIND as RFI, ModsIncSvcBulleting as MAT From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<SAS_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">R2</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">1</" + oleDbDataReader.GetName(2).ToString() + ">");
if (oleDbDataReader.GetValue(3).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(3).ToString() + ">" + oleDbDataReader.GetValue(3).ToString() + "</" + oleDbDataReader.GetName(3).ToString() + ">");
}
}
stringBuilder.Append("</SAS_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateATT(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT TCRCD as TRF, ODT From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<ATT_Segment>");
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
}
else
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
}
stringBuilder.Append("</ATT_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateLNK(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT RANumber as RTI From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<LNK_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
stringBuilder.Append("</LNK_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateAPI(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT AircraftEngType as AET,APUSN as EMS From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<API_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
}
stringBuilder.Append("</API_Segment>");
}
return(stringBuilder.ToString());
}
internal void GenerateXML(DateTime StartDate, DateTime EndDate)
{
try
{
this.sd = string.Format("{0:MM/dd/yyyy}", (object)StartDate);
this.ed = string.Format("{0:MM/dd/yyyy}", (object)EndDate);
OleDbConnection oleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source='" + this.fpath + "';Jet OLEDB:Database Password=''");
oleDbConnection.Open();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT DISTINCT OprCode as OPR,Customer FROM tblRepairHistory WHERE SerialNumber LIKE 'cj%' AND OprCode not in(NULL,'0') AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# ", oleDbConnection).ExecuteReader();
new OleDbDataAdapter(new OleDbCommand("SELECT DISTINCT OprCode as OPR,Customer FROM tblRepairHistory WHERE SerialNumber LIKE 'cj%' AND OprCode not in(NULL,'0') AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# ", oleDbConnection)).Fill(new DataSet());
StringBuilder stringBuilder = new StringBuilder();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<?xml version='1.0' encoding='UTF-8' ?> ");
stringBuilder.Append("<ATA_InformationSet xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='Modular_Schema\\ATA_InformationSet.xsd' id='R2009.1' version='1.0'>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<ReliabilityData>");
stringBuilder.Append("<ShopFindings version='2.00'>");
stringBuilder.Append("<HDR_Segment>");
stringBuilder.Append("<CHG>N</CHG>");
stringBuilder.Append("<ROC>10933</ROC>");
stringBuilder.Append("<RDT>" + StartDate.ToString("yyyy-MM-dd") + "</RDT>");
stringBuilder.Append("<RSD>" + EndDate.ToString("yyyy-MM-dd") + "</RSD>");
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<RON>Avionic Instruments LLC</RON>");
if (oleDbDataReader.GetValue(0).ToString() == "ZZZZZ")
{
stringBuilder.Append("<WHO>" + oleDbDataReader.GetValue(1).ToString() + "</WHO>");
}
stringBuilder.Append("</HDR_Segment>");
stringBuilder.Append(this.GenerateShopFindingsDetails(oleDbDataReader.GetValue(0).ToString(), oleDbDataReader.GetValue(1).ToString(), oleDbConnection));
stringBuilder.Append("</ShopFindings>");
stringBuilder.Append("</ReliabilityData>");
}
stringBuilder.Append("</ATA_InformationSet>");
}
string str = "report_" + DateTime.Now.Date.ToString("MM-dd-yy") + ".xml";
Directory.CreateDirectory(".\\reports");
if (File.Exists(".\\reports\\" + str))
{
int num = (int)this.saveFileDialog1.ShowDialog();
TextWriter textWriter = (TextWriter) new StreamWriter(this.fpath_new);
Directory.GetParent(this.fpath_new).ToString();
Process.Start(Directory.GetParent(this.fpath_new).ToString());
textWriter.Write(stringBuilder.ToString());
textWriter.Close();
}
else
{
TextWriter textWriter = (TextWriter) new StreamWriter(".\\reports\\" + str);
Process.Start(".\\reports");
textWriter.Write(stringBuilder.ToString());
textWriter.Close();
}
oleDbConnection.Close();
Application.Exit();
}
catch (OleDbException ex)
{
throw ex;
}
}
static void Main(string[] args)
{
#region Init
Console.WriteLine("建立数据库...");
OleDbDataReader reader;
string dConnS = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
bool flag = true;
SqlConnection dbConn;
flag:
dbConn = new SqlConnection(dConnS);
try
{
dbConn.Open();
}
catch (SqlException exp)
{
if (flag)
{
dConnS = "Data Source=(local);Initial Catalog=master;Integrated Security=True";
flag = false;
}
else
{
Console.WriteLine("无法自动连接数据库，请手动输入DataSource。\n例如(local)\\SQLEXPRESS");
dConnS = "Data Source=" + Console.ReadLine() + ";Initial Catalog=master;Integrated Security=True";
}
goto flag;
}
SqlDataReader re;
re = new SqlCommand("select count(*) from sys.databases where name='plent' or name ='insect' or name ='users'", dbConn).ExecuteReader();
re.Read();
if (int.Parse(re.GetValue(0).ToString()) > 0)
{
re.Close();
Console.WriteLine("检测到已存在数据库plent或insect或users。\n即将删库并重新建立。输入9确认，按任意键结束程序。");
if (Console.ReadKey().KeyChar == 57)
{
try
{
new SqlCommand("drop database plent,insect,users", dbConn).ExecuteNonQuery();
Console.WriteLine("删库完成");
}
catch (Exception exp) { }
}
else
{
dbConn.Close();
return;
}
}
else
{
re.Close();
}
new SqlCommand("create database insect\ncreate database plent\ncreate database users", dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("建库完成");
#endregion
#region users
Console.WriteLine("用户库建表...");
dbConn = new SqlConnection("Data Source=(local)" + (flag?"\\SQLEXPRESS":"") + ";Initial Catalog=users;Integrated Security=True");
dbConn.Open();
new SqlCommand(File.ReadAllText(".\\sqlusers.txt", Encoding.Default), dbConn).ExecuteNonQuery();
dbConn.Close();
#endregion
#region insect
Console.WriteLine("昆虫库建表...");
dbConn = new SqlConnection("Data Source=(local)" + (flag ? "\\SQLEXPRESS" : "") + ";Initial Catalog=insect;Integrated Security=True");
dbConn.Open();
new SqlCommand(File.ReadAllText(".\\sqlinsect.txt", Encoding.Default), dbConn).ExecuteNonQuery();
string eConnS = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=.\\insect.xlsx;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(eConnS);
dbConn.Close();
string insert;
Console.WriteLine("填充目表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [目$]", OleConn).ExecuteReader();
insert = "INSERT INTO orders VALUES ";
if (reader.Read())
{
insert += "('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "')";
}
while (reader.Read())
{
insert += ",('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "')";
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充科表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [科$]", OleConn).ExecuteReader();
insert = "INSERT INTO family VALUES ";
if (reader.Read())
{
insert += "('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select oname from orders where c_oname='" + reader.GetValue(3) + "'))";
}
while (reader.Read())
{
insert += ",('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select oname from orders where c_oname='" + reader.GetValue(3) + "'))";
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充种表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [种$]", OleConn).ExecuteReader();
insert = "INSERT INTO species VALUES ";
if (reader.Read())
{
insert += "(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select fname from family where c_fname='" + reader.GetValue(3) + "'))";
}
while (reader.Read())
{
insert += ",(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select fname from family where c_fname='" + reader.GetValue(3) + "'))";
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充特征表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [特征$]", OleConn).ExecuteReader();
insert = "INSERT INTO features VALUES ";
if (reader.Read())
{
insert += "(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "','" + reader.GetValue(3) + "','" + reader.GetValue(4) + "'," + (reader.GetValue(5).ToString())[0] + ")";
}
while (reader.Read())
{
try { insert += ",(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "','" + reader.GetValue(3) + "','" + reader.GetValue(4) + "'," + (reader.GetValue(5).ToString())[0] + ")"; }
catch (Exception exp) { }
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("昆虫库添加存储过程");
dbConn.Open();
string si = File.ReadAllText(".\\insect.txt", Encoding.Default);
int leni = si.Length;
int lasti = 0;
for (int i = 0; i < leni; i++)
{
if (si[i] == ';')
{
new SqlCommand(Extract(si, lasti, i), dbConn).ExecuteNonQuery();
lasti = i + 1;
}
}
Console.WriteLine("昆虫库完成");
#endregion
#region plent
Console.WriteLine("植物库建表...");
dConnS = "Data Source=(local)" + (flag ? "\\SQLEXPRESS" : "") + ";Initial Catalog=plent;Integrated Security=True";
eConnS = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=.\\plent.xlsx;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'";
OleConn = new OleDbConnection(eConnS);
dbConn = new SqlConnection(dConnS);
dbConn.Open();
new SqlCommand(File.ReadAllText(".\\sqlplent.txt", Encoding.Default), dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充纲表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [纲$]", OleConn).ExecuteReader();
insert = "INSERT INTO class VALUES ";
if (reader.Read())
{
insert += "('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "')";
}
while (reader.Read())
{
insert += ",('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "')";
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充科表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [科$]", OleConn).ExecuteReader();
while (reader.Read())
{
insert += "INSERT INTO family VALUES " + "('" + reader.GetValue(0) + "','" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select cname from class where c_cname='" + reader.GetValue(3) + "'))";
}
OleConn.Close();
dbConn.Open();
try { new SqlCommand(insert, dbConn).ExecuteNonQuery(); }
catch (Exception exp) { }
dbConn.Close();
Console.WriteLine("填充种表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [种$]", OleConn).ExecuteReader();
insert = "INSERT INTO species VALUES ";
if (reader.Read())
{
insert += "(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select fname from family where c_fname='" + reader.GetValue(3) + "'))";
}
while (reader.Read())
{
if (reader.GetValue(0).ToString() == "")
{
break;
}
else
{
insert += ",(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "',(select fname from family where c_fname='" + reader.GetValue(3) + "'))";
}
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("填充特征表...");
OleConn.Open();
reader = new OleDbCommand("SELECT * FROM [特征$]", OleConn).ExecuteReader();
insert = "INSERT INTO features VALUES ";
if (reader.Read())
{
insert += "(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "','" + reader.GetValue(3) + "','" + reader.GetValue(4) + "','" + reader.GetValue(5) + "','" + reader.GetValue(6) + "')";
}
while (reader.Read())
{
insert += ",(" + reader.GetValue(0) + ",'" + reader.GetValue(1) + "','" + reader.GetValue(2) + "','" + reader.GetValue(3) + "','" + reader.GetValue(4) + "','" + reader.GetValue(5) + "','" + reader.GetValue(6) + "')";
}
OleConn.Close();
dbConn.Open();
new SqlCommand(insert, dbConn).ExecuteNonQuery();
dbConn.Close();
Console.WriteLine("植物库填充存储过程...");
dbConn = new SqlConnection(dConnS);
dbConn.Open();
string sp = File.ReadAllText(".\\plent.txt", Encoding.Default);
int len = sp.Length;
int lastp = 0;
for (int i = 0; i < len; i++)
{
if (sp[i] == ';')
{
new SqlCommand(Extract(sp, lastp, i), dbConn).ExecuteNonQuery();
lastp = i + 1;
}
}
dbConn.Close();
#endregion
Console.WriteLine("植物库完成\n建立数据库完成\n按任意键退出");
Console.ReadKey();
}
