datagenerator.aspx

掌握学习.net开发的非常好的资料

<%@ Page Language="c#" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Common" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Globalization" %>

<script runat="server">
void Page_Init(Object Src, EventArgs E ) {
   AddSampleData();  
    
}
void AddSampleData() 
{
    	int result =0;
    	DateTime singleDate =DateTime.Now;
    	string [] customers = {"David","Moe","Ali","John","Joe","Kei","Pearl","Aida","Ain","Barbi","Bard","Bo","David","Moe","Ali","John","Joe","Kei","Pearl","Aida","Ain","Barbi","Bard"};
		SqlConnection conn = new SqlConnection("server=serverName;uid=user ID;pwd=password;database=db Name");
    	conn.Open();
    	SqlCommand cmd = conn.CreateCommand();
    	StringBuilder sqlStatement;
    	CultureInfo cfdb = new CultureInfo("en-US");
    	
    	Random r = new Random();
		Random r2 = new Random();
    	for(int y=2001;y<=2002;y++)
    	{
			for (int m=1;m<=12;m++)
			{
				int MaxDays = DateTime.DaysInMonth(y,m);
				for (int d=1;d<MaxDays+1;d++)
				{
					int orderPerDay = r.Next(1,23);
					
					for(int h=1;h<orderPerDay+1;h++)
					{
						int q = r.Next(1,23);
						double total =(double) q*12.99;
						int itemId = r2.Next(1,4);
						singleDate = new DateTime(y,m,d,q,q,q);

    
						sqlStatement = new StringBuilder("INSERT INTO Orders VALUES('",256);
						sqlStatement.Append(customers[h]);
						sqlStatement.Append("', ");
						sqlStatement.Append(Convert.ToString(q));
						sqlStatement.Append(", ");
						sqlStatement.Append(total.ToString());
						sqlStatement.Append(", '");
						sqlStatement.Append(singleDate.ToString(cfdb));
						sqlStatement.Append("',");
						sqlStatement.Append(itemId.ToString());
						sqlStatement.Append(")");
						cmd.CommandText = sqlStatement.ToString();
						result = cmd.ExecuteNonQuery();
						if(result!=1)
						 break;
					}
					if(result!=1)
						 break;
				}  
				if(result!=1)
						 break;
    	}
    }
    
    if(result ==1)
    	status.Text = "sample data successfully added to Orders table.<br>";
    else
    	status.Text ="sample generator failed.<br>";
    	
    	
    	
    //Add data to Financial table
    singleDate = new DateTime(2003,6,1);

    for(int d=0;d<29;d++)
    {
    	singleDate = singleDate.AddDays(1);
    	if(singleDate.DayOfWeek == DayOfWeek.Saturday || singleDate.DayOfWeek == DayOfWeek.Sunday)
    	  		continue;
    	
    	for(int h=0;h<5;h++)
		{
			double stockPrice = r.Next(5,15) + r.NextDouble();
			singleDate = singleDate.AddHours(r.Next(0,5));
			sqlStatement = new StringBuilder("INSERT INTO Financial VALUES(",256);
			sqlStatement.Append(stockPrice.ToString("0.00"));
			sqlStatement.Append(", '");
			sqlStatement.Append(singleDate.ToString(cfdb));
			sqlStatement.Append("', ");
			sqlStatement.Append(r.Next(25,1000));
			sqlStatement.Append(")");
			Console.WriteLine("<br>sqlStatement=" + sqlStatement.ToString());
			cmd.CommandText = sqlStatement.ToString();
			result = cmd.ExecuteNonQuery();
			if(result!=1)
				break;
    	
		}
		if(result!=1)
			break;    
	}
	if(result ==1)
    	status.Text += "sample data successfully added to Financial table.<br>";
    else
    	status.Text +="sample generator failed.<br>";


    
    //Add data to ganttSample table
    cmd.CommandText = "INSERT INTO ganttSample VALUES('Research',0,6,'David',100,'" + (new DateTime(2002,1,1)).ToString(cfdb) + "','" + (new DateTime(2002,4,30)).ToString(cfdb) + "')";
	cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Research',2,7,'Smith',100,'" + (new DateTime(2002,2,1)).ToString(cfdb) + "','" + (new DateTime(2002,3,28)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Implementation',9,14,'Joe',50,'" + (new DateTime(2002,10,1)).ToString(cfdb) + "','" + (new DateTime(2002,5,25)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Test',14,16,'Mary',0,'" + (new DateTime(2002,3,1)).ToString(cfdb) + "','" + (new DateTime(2002,7,30)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Package',16,17,'Mary',0,'" + (new DateTime(2002,8,1)).ToString(cfdb) + "','" + (new DateTime(2002,8,30)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Design',6,8,'David',100,'" + (new DateTime(2002,5,1)).ToString(cfdb) + "','" + (new DateTime(2002,8,10)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Design',7,9,'Smith',100,'" + (new DateTime(2002,4,1)).ToString(cfdb) + "','" + (new DateTime(2002,10,10)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Test',14,17,'Joe',0,'" + (new DateTime(2002,5,26)).ToString(cfdb) + "','" + (new DateTime(2002,6,10)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Test',14,16,'David',0,'" + (new DateTime(2002,5,1)).ToString(cfdb) + "','" + (new DateTime(2002,7,1)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO ganttSample VALUES('Test',14,16,'Smith',0,'" + (new DateTime(2002,4,1)).ToString(cfdb) + "','" + (new DateTime(2002,7,10)).ToString(cfdb) + "')";
	result = cmd.ExecuteNonQuery();
	
	if(result ==1)
    	status.Text += "sample data successfully added to ganttSample table.<br>";
    else
    	status.Text +="sample generator failed.<br>";

	
	 //Add data to Employees table
    cmd.CommandText = "INSERT INTO Employees VALUES('Aida',45500,'Sales','[e-mail]','[phone]')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO Employees VALUES('Ain',30500,'Sales','[e-mail]','[phone]')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO Employees VALUES('John',31200,'Service','[e-mail]','[phone]')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO Employees VALUES('David',41400,'Service','[e-mail]','[phone]')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO Employees VALUES('Joe',26000,'Service','[e-mail]','[phone]')";
	result = cmd.ExecuteNonQuery();
	
	if(result ==1)
    	status.Text += "sample data successfully added to Employees table.<br>";
    else
    	status.Text +="sample generator failed.<br>";

	
	 //Add data to SampleData table
    cmd.CommandText = "INSERT INTO SampleData VALUES(1,2,1,'point 1')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO SampleData VALUES(8,3,2,'point 2')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO SampleData VALUES(6,7,3,'point 3')";
	result = cmd.ExecuteNonQuery();
	cmd.CommandText = "INSERT INTO SampleData VALUES(2,8,0,'point 4')";
	result = cmd.ExecuteNonQuery();