database.cs

固定资产的终端程序！里面有对数据的处理。 好东西

using System;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using Lib;
using System.Data;
namespace AssetCheck.Libs
{
	/// <summary>
	/// DataBase 的摘要说明。
	/// </summary>
	public class DataBase
	{
		public DataBase()
		{}
		public static bool SetDataBase()
		{
			Wait wait=new Wait();
			try
			{
				wait.Show();
				wait.labMessage.Text="正在加载数据库,请稍后...";
				wait.Update();
				int ErrorRecord=0;
				string strsr="";
					
				string str0="";
				string str1="";
				string str2="";
				string str3="";
				string str4="";
				string str5="";
				string str6="";
				
					
				int Record=0;

				E.Space=";";
				string[] a;
				if (File.Exists ("Asset.sdf"))
				{
					E.Beep("",50,2670,2);
					E.MessageShow(false,"是否重新加载数据盘点?\n如果继续盘点,请选择'否'.");
					if (YNMessage.YN)
					{
						if ((!File.Exists("lib.txt"))||(!File.Exists("User.txt"))||(!File.Exists("ZT.txt")))
						{
							E.Beep("",50,2670,2);
							E.MessageShow(true,"没有数据文件,\n请先下载文件!");
							return false;
						}
						if (E.conn.State==ConnectionState.Open)
						{
							E.conn.Close();
						}
						File.Delete("Asset.sdf");
						if (File.Exists("Platform\\DataBank\\AssetPD.txt"))
						{
							File.Delete("Platform\\DataBank\\AssetPD.txt");
						}
						if (File.Exists("Platform\\DataBank\\AssetZTTZ.txt"))
						{
							File.Delete("Platform\\DataBank\\AssetZTTZ.txt");
						}
						if (File.Exists("Platform\\DataBank\\Asset.sdf"))
						{
							File.Delete("Platform\\DataBank\\Asset.sdf");
						}
					}
					else
					{
						return true;
					}
				}
				else if (File.Exists("Platform\\DataBank\\Asset.sdf"))
				{
					wait.labMessage.Text="正在还原数据库,请稍后...";
					wait.Update();
					
					if(E.UpDownFile("Platform\\DataBank\\Asset.sdf","Asset.sdf",true))
					{
					}
					else
					{
						E.Beep("",50,2670,2);
						E.MessageShow(true,"还原数据库失败!");
						if (File.Exists("Asset.sdf"))
						{
							File.Delete("Asset.sdf");
						}
						return false;
					}
					if (File.Exists("Platform\\DataBank\\AssetPD.txt"))
					{
						FileStream fs=null;
						StreamReader sr=null;
						SqlCeConnection conn=null;
						SqlCeCommand cmd=null;
						try
						{
							fs=new FileStream("Platform\\DataBank\\AssetPD.txt",FileMode.Open,FileAccess.Read);
							sr = new StreamReader(fs,System.Text.Encoding.GetEncoding("GB2312"));
							conn=new SqlCeConnection("Data Source =Asset.sdf");
							cmd=conn.CreateCommand();
							conn.Open();
							while(sr.Peek()>-1)
							{
								strsr=sr.ReadLine();
       
								try
								{
									a = E.GetArySpace(strsr); 

									str0=a[0].Trim();
				
									str1=a[1].Trim();
							
									cmd.CommandText="update Asset set UserID='"+str1+"' where BarCodes='"+str0+"'";
									try
									{
										cmd.ExecuteNonQuery();
									}
									catch
									{
										E.Beep("",50,2670,2);
										E.MessageShow(true,"还原失败,请重新启动程序!");
										if (File.Exists("Asset.sdf"))
										{
											File.Delete("Asset.sdf");
										}
										Application.Exit();
										return false;
									}
									Record++;
									wait.labMessage.Text="已还原: "+Record.ToString()+" 条";
									wait.Update();
								}
								catch
								{ 
								}
							}
						}
						finally
						{
							fs.Flush();
							fs.Close();
							sr.Close();
							
							if (cmd!=null)
								cmd.Dispose();
							if (conn!=null)
								conn.Dispose();
						}
					}
					if (File.Exists("Platform\\DataBank\\AssetZTTZ.txt"))
					{
						FileStream fs=null;
						StreamReader sr=null;
						SqlCeConnection conn=null;
						SqlCeCommand cmd=null;
						try
						{
							fs=new FileStream("Platform\\DataBank\\AssetZTTZ.txt",FileMode.Open,FileAccess.Read);
							sr = new StreamReader(fs,System.Text.Encoding.GetEncoding("GB2312"));
							conn=new SqlCeConnection("Data Source =Asset.sdf");
							cmd=conn.CreateCommand();
							conn.Open();
							while(sr.Peek()>-1)
							{
								strsr=sr.ReadLine();
       
								try
								{
									a = E.GetArySpace(strsr); 

									str0=a[0].Trim();
				
									str1=a[1].Trim();

									str2=a[2].Trim();
									cmd.CommandText="select Count(BarCodes) from ZTTZ where BarCodes='"+str0+"'";
									//MessageBox.Show(cmd.CommandText);
									int ICount=0;
									try
									{
										ICount=(int)cmd.ExecuteScalar();
									}
									catch{}
									if (ICount>0)
									{
										cmd.CommandText="update ZTTZ set Status='"+str1+"',UserID='"+str2+"' where BarCodes='"+str0+"'";
										//MessageBox.Show(cmd.CommandText);
										try
										{
											cmd.ExecuteNonQuery();
										}
										catch
										{
											E.MessageShow(true,"还原失败,请重新启动程序!");
											if (File.Exists("Asset.sdf"))
											{
												File.Delete("Asset.sdf");
											}
											Application.Exit();
											return false;
										}
									}
									else
									{
										cmd.CommandText="insert into ZTTZ(BarCodes,Status,UserID) values('"+str0+"','"+str1+"','"+str2+"')";
										//MessageBox.Show(cmd.CommandText);
										try
										{
											cmd.ExecuteNonQuery();
										}
										catch
										{
											E.MessageShow(true,"还原失败,请重新启动程序!");
											if (File.Exists("Asset.sdf"))
											{
												File.Delete("Asset.sdf");
											}
											Application.Exit();
											return false;
										}
									}
									Record++;
									wait.labMessage.Text="已还原: "+Record.ToString()+" 条";
									wait.Update();
								}
								catch
								{ 
								}
							}
						}
						finally
						{
							fs.Flush();
							fs.Close();
							sr.Close();
							if (cmd!=null)
								cmd.Dispose();
							if (conn!=null)
								conn.Dispose();
						}
					}
					return true;
				}
				if ((!File.Exists("lib.txt"))||(!File.Exists("User.txt"))||(!File.Exists("ZT.txt")))
				{
					E.Beep("",50,2670,2);
					E.MessageShow(true,"没有数据文件,\n请先下载文件!");
					return false;

				}
				wait.labMessage.Text="正在加载数据库,请稍后...";
				wait.Update();
				
				SqlCeEngine engine = null;
				SqlCeConnection conn1=null;
				SqlCeCommand cmd1=null;
				FileStream fs1=null;
				StreamReader sr1=null;
				TextWriter w=null;
				w=new StreamWriter("Error.txt",false,System.Text.Encoding.Default);
				try
				{
					if (File.Exists("Asset.sdf"))
					{
						File.Delete("Asset.sdf");
					}
					engine = new SqlCeEngine ("Data Source =Asset.sdf");
					engine.CreateDatabase ();
					conn1 = new SqlCeConnection ("Data Source =Asset.sdf");
					conn1.Open();
					cmd1 = conn1.CreateCommand();
					cmd1.CommandText = "CREATE TABLE Asset(BarCodes nvarchar(28),Names nvarchar(50),Spec nvarchar(50),Department nvarchar(50),Manager nvarchar(12),Startdate nvarchar(30),Status nvarchar(30),UserID nvarchar(10))";
					cmd1.ExecuteNonQuery();
					cmd1.CommandText = "CREATE TABLE ZTTZ(BarCodes nvarchar(28),Status nvarchar(30),UserID nvarchar(10))";
					cmd1.ExecuteNonQuery();
					cmd1.CommandText = "CREATE TABLE ZT(Code nvarchar(10),Names nvarchar(30))";
					cmd1.ExecuteNonQuery();
					cmd1.CommandText = "CREATE TABLE Users(ID nvarchar(10),Names nvarchar(10),Pass nvarchar(40))";
					cmd1.ExecuteNonQuery();
					E.TableGood(cmd1,"Asset","BarCodes");
					E.TableGood(cmd1,"ZTTZ","BarCodes");


					
					
					fs1=new FileStream("lib.txt",FileMode.Open,FileAccess.Read);
					sr1 = new StreamReader(fs1,System.Text.Encoding.GetEncoding("GB2312"));
				
					while(sr1.Peek()>-1)
					{
						strsr=sr1.ReadLine();

						try
						{
							a = E.GetArySpace(strsr); 

							str0=a[0].Trim();

							str1=a[1].Trim();

							str2=a[2].Trim();

							str3=a[3].Trim();

							str4=a[4].Trim();

							str5=a[5].Trim();

							str6=a[6].Trim();

							
                            //Company nvarchar(50),
							cmd1.CommandText = "insert into Asset(BarCodes,Names,Spec,Department,Manager,StartDate,Status,UserID) values('"+str0+"','"+str1+"','"+str2+"','"+str3+"','"+str4+"','"+str5+"','"+str6+"','0')";
							cmd1.ExecuteNonQuery();

							Record++;
							wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
							wait.Update();
						}
						catch
						{ 
							try
							{
								if (strsr.Trim()!="")
								{
									str0=E.strR(str0);
									str1=E.strR(str1);
									str2=E.strR(str2);
									str3=E.strR(str3);
									str4=E.strR(str4);
									str5=E.strR(str5);
									str6=E.strR(str6);

									cmd1.CommandText = "insert into Asset(BarCodes,Names,Spec,Department,Manager,StartDate,Status,UserID) values('"+str0+"','"+str1+"','"+str2+"','"+str3+"','"+str4+"','"+str5+"','"+str6+"','0')";
									cmd1.ExecuteNonQuery();

									Record++;
									wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
									wait.Update();
								}
							}
							catch
							{
								w.WriteLine(strsr);
								ErrorRecord++;
							
							}
						}
					}
					fs1.Flush();
					fs1.Close();
					sr1.Close();
					fs1=new FileStream("ZT.txt",FileMode.Open,FileAccess.Read);
					sr1 = new StreamReader(fs1,System.Text.Encoding.GetEncoding("GB2312"));
				
					while(sr1.Peek()>-1)
					{
						strsr=sr1.ReadLine();
       
						try
						{
							a = E.GetArySpace(strsr); 

							str0=a[0].Trim();
				
							str1=a[1].Trim();

					
							cmd1.CommandText = "INSERT INTO ZT(Code,Names) VALUES('"+str0+"','"+str1+"')";
							cmd1.ExecuteNonQuery();

							Record++;
							wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
							wait.Update();
						}
						catch
						{ 
							try
							{
								if (strsr.Trim()!="")
								{
									str0=E.strR(str0);
									str1=E.strR(str1);
									cmd1.CommandText = "INSERT INTO ZT(Code,Names) VALUES('"+str0+"','"+str1+"')";
									cmd1.ExecuteNonQuery();

									Record++;
									wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
									wait.Update();
								}
							}
							catch
							{
							  
							
								w.WriteLine(strsr);
								ErrorRecord++;
							
							}
						}
					}
					fs1.Flush();
					fs1.Close();
					sr1.Close();
					fs1=new FileStream("User.txt",FileMode.Open,FileAccess.Read);
					sr1 = new StreamReader(fs1,System.Text.Encoding.GetEncoding("GB2312"));
				
					while(sr1.Peek()>-1)
					{
						strsr=sr1.ReadLine();
       
						try
						{
							a = E.GetArySpace(strsr); 

							str0=a[0].Trim();
				
							str1=a[1].Trim();

							str2=a[2].Trim();

						
							cmd1.CommandText = "INSERT INTO Users(ID,Names,Pass) VALUES('"+str0+"','"+str1+"','"+str2+"')";
							//w.WriteLine(cmd1.CommandText);
							cmd1.ExecuteNonQuery();

							Record++;
							wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
							wait.Update();
						}
						catch
						{ 
							try
							{
								if (strsr.Trim()!="")
								{
									str0=E.strR(str0);
									str1=E.strR(str1);
									str2=E.strR(str2);
									

									cmd1.CommandText = "INSERT INTO Users(ID,Names,Pass) VALUES('"+str0+"','"+str1+"','"+str2+"')";
									//w.WriteLine(cmd1.CommandText);
									cmd1.ExecuteNonQuery();

									Record++;
									wait.labMessage.Text="已加载: "+Record.ToString()+" 条";
									wait.Update();
								}
							}
							catch
							{
								w.WriteLine(strsr);
								ErrorRecord++;
							
							}
						}
					}
				}
				catch(Exception EE)
				{
					E.Beep("",50,2670,2);
					E.MessageShow(true,"加载数据库失败:\n"+EE.ToString());
					return false;
				}
				finally
				{
					engine.Dispose();
					conn1.Dispose();
					cmd1.Dispose();
					fs1.Flush();
					fs1.Close();
					sr1.Close();
					w.Close();
				}
				
				wait.labMessage.Text="正在处理数据,请稍后...";
				wait.labMessage.Text="";
				wait.Update();
				if (!Directory.Exists("Platform\\DataBank"))
				{
					Directory.CreateDirectory("Platform\\DataBank");
				}
				if(E.UpDownFile("Asset.sdf","Platform\\DataBank\\Asset.sdf",true))
				{
					if (ErrorRecord>0)
					{
						E.Beep("",50,2670,2);
					
						E.MessageShow(true,"有:"+ErrorRecord.ToString()+"条记录没有加载进数据库,存放于:Error.txt!");
					}
					return true;
				}
				else
				{
					E.Beep("",50,2670,2);
					E.MessageShow(true,"加载数据库失败!");
					return false;
				}
				
				return true;
			} 
			finally
			{
				wait.Dispose();			
			}
		}
	}
}