C# Insert Test Data in Sql Server DataBase

最新推荐文章于 2024-04-28 17:27:20 发布

漂泊_人生

最新推荐文章于 2024-04-28 17:27:20 发布

阅读量270

点赞数

分类专栏： C# 控制台 SQL

本文链接：https://blog.csdn.net/u013934107/article/details/81659969

版权

C# 控制台同时被 2 个专栏收录

68 篇文章 3 订阅

订阅专栏

SQL

68 篇文章 1 订阅

订阅专栏

1.c# Source code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using System.Data;
using System.ComponentModel;
using System.Collections;
using System.Web;
using System.Timers;

namespace Aging_TestData_Inset_SQLDataBase
{
public class Program
{
public static String[] KeyValue = { "NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL",
"NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL"};
public static String[] Smple = { "BurnInMarkFile","Client_SN", "LineBody", "JobNumber", "Mac_Address","Product_Name","Test_Start_time", "Test_Stop_time",
"CPU_TEST_Operations","Memory_TEST_Operations","_2D_Graphics_TEST_Operations","_3D_Graphics_TEST_Operations",
"Sound_TEST_Operations","Disk_TEST_Operations","Network_TEST_Operations",
"TEST_Result","Date_Time","SERVER","DATABASE","UID","PWD","StoredProcedure"};
static int Main(string[] args)
{
CfgProcess MyCfg = new CfgProcess("config.ini");//读取配置信息
MyCfg.Log_Str_Intercepting(KeyValue[0]);//读取测试LOG信息
TestDataInsertSqlDataBase MyTestDataInsert = new TestDataInsertSqlDataBase(KeyValue[1], KeyValue[2], KeyValue[3], KeyValue[4], KeyValue[5],
KeyValue[6], KeyValue[7], KeyValue[8], KeyValue[9], KeyValue[10], KeyValue[11], KeyValue[12], KeyValue[13], KeyValue[14], KeyValue[15],
KeyValue[16], KeyValue[17], KeyValue[18], KeyValue[19], KeyValue[20], KeyValue[21]);
if (MyTestDataInsert.InsertTestDataInputSqlDataBase() == false) return 1;
return 0;
}
}

public class TestDataInsertSqlDataBase : Program { //测试数据插入数据库
private String Client_SN;
private String LineBody;
private String JobNumber;
private String Mac_Address;
private String Product_Name;
private String Test_Start_time;
private String Test_Stop_time;
private String CPU_TEST_Operations;
private String Memory_TEST_Operations;
private String _2D_Graphics_TEST_Operations;
private String _3D_Graphics_TEST_Operations;
private String Sound_TEST_Operations;
private String Disk_TEST_Operations;
private String Network_TEST_Operations;
private String TEST_Result;
private String Date_Time;
private String SERVER;
private String DATABASE;
private String UID;
private String PWD;
private String StoredProcedure;

public TestDataInsertSqlDataBase(String Client_SN, String LineBody, String JobNumber, String Mac_Address, String Product_Name,
String Test_Start_time, String Test_Stop_time, String CPU_TEST_Operations, String Memory_TEST_Operations, String _2D_Graphics_TEST_Operations,
String _3D_Graphics_TEST_Operations, String Sound_TEST_Operations, String Disk_TEST_Operations, String Network_TEST_Operations,
String TEST_Result, String Date_Time, String SERVER, String DATABASE, String UID, String PWD, String StoredProcedure)
{
this.Client_SN = Client_SN;
this.LineBody = LineBody;
this.JobNumber = JobNumber;
this.Mac_Address = Mac_Address;
this.Product_Name = Product_Name;
this.Test_Start_time = Test_Start_time;
this.Test_Stop_time = Test_Stop_time;
this.CPU_TEST_Operations = CPU_TEST_Operations;
this.Memory_TEST_Operations = Memory_TEST_Operations;
this._2D_Graphics_TEST_Operations = _2D_Graphics_TEST_Operations;
this._3D_Graphics_TEST_Operations = _3D_Graphics_TEST_Operations;
this.Sound_TEST_Operations = Sound_TEST_Operations;
this.Disk_TEST_Operations = Disk_TEST_Operations;
this.Network_TEST_Operations = Network_TEST_Operations;
this.TEST_Result = TEST_Result;
this.Date_Time = Date_Time;
this.SERVER = SERVER;
this.DATABASE = DATABASE;
this.UID = UID;
this.PWD = PWD;
this.StoredProcedure = StoredProcedure;
}

public bool InsertTestDataInputSqlDataBase()//数据插入数据库与处理
{
bool Flag = false;
try
{
SqlConnection conn = new SqlConnection("server=" + this.SERVER + ";database=" + this.DATABASE + ";uid=" + this.UID + ";pwd=" + this.PWD);
conn.Open();
if (conn.State != ConnectionState.Open)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Sql DataBase Connection Fail!");
Console.ForegroundColor = ConsoleColor.White;
Flag = false;
return Flag;
}
SqlCommand cmd = new SqlCommand(this.StoredProcedure, conn);//设置命令行启动
cmd.CommandType = CommandType.StoredProcedure;//启动数据库存储过程
cmd.Parameters.Add("@Client_SN",this.Client_SN);
cmd.Parameters.Add("@LineBody",this.LineBody);
cmd.Parameters.Add("@JobNumber",this.JobNumber);
cmd.Parameters.Add("@Produnct_Name",this.Product_Name);
cmd.Parameters.Add("@Mac_Address",this.Mac_Address);
cmd.Parameters.Add("@Test_Start_time",this.Test_Start_time);
cmd.Parameters.Add("@Test_Stop_time",this.Test_Stop_time);
cmd.Parameters.Add("@CPU_TEST_Operations", this.CPU_TEST_Operations);
cmd.Parameters.Add("@Memory_TEST_Operations",this.Memory_TEST_Operations);
cmd.Parameters.Add("@_2D_Graphics_TEST_Operations",this._2D_Graphics_TEST_Operations);
cmd.Parameters.Add("@_3D_Graphics_TEST_Operations",this._3D_Graphics_TEST_Operations);
cmd.Parameters.Add("@Sound_TEST_Operations",this.Sound_TEST_Operations);
cmd.Parameters.Add("@Disk_TEST_Operations",this.Disk_TEST_Operations);
cmd.Parameters.Add("@Network_TEST_Operations",this.Network_TEST_Operations);
cmd.Parameters.Add("@TEST_Result",this.TEST_Result);
cmd.Parameters.Add("@Date_Time",this.Date_Time);
cmd.Parameters.Add("@rs",1);
cmd.Parameters["@rs"].Direction = ParameterDirection.Output;//启动输出返回
cmd.ExecuteScalar();
if ((int)cmd.Parameters["@rs"].Value == 0)
{
conn.Close();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Insert Test DataBase Successfully!");
Console.ForegroundColor = ConsoleColor.White;
Flag = true;
}
else
{
conn.Close();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Failed Insert test Data Into DataBase!");
Console.ForegroundColor = ConsoleColor.White;
Flag = false;
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Sql DataBase Data Connection Or Process Fail");
Console.WriteLine(ex.ToString());
Console.ForegroundColor = ConsoleColor.White;
Flag = false;
}
return Flag;
}

}
public class CfgProcess : Program {//读取配置信息
public CfgProcess(String CfgFileName)
{
try
{
FileStream fs = new FileStream(CfgFileName,FileMode.Open,FileAccess.Read);
StreamReader sr = new StreamReader(fs,Encoding.Default);
String Temp = String.Empty;
while ((Temp = sr.ReadLine()) != null)
{
String[] Array = Temp.Split(new String[] { "=" }, StringSplitOptions.RemoveEmptyEntries);
int n = 0;
foreach (String Str in Smple)
{
if (Array[0].Trim() == Str)
{
if (Array[1].Trim().Length >= 2)
{
KeyValue[n] = Array[1];
break;
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Config " + CfgFileName + " File " + Array[0].Trim() + " Item Content Is null!");
Console.ForegroundColor = ConsoleColor.White;
Environment.Exit(1);
}
}
n++;
}
}
sr.Close();
fs.Close();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(CfgFileName+" Config File Read Or Open Error!");
Console.WriteLine(ex.ToString());
Console.ForegroundColor = ConsoleColor.White;
Environment.Exit(1);
}
String NowDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
KeyValue[16] = NowDateTime;
}

public bool Log_Str_Intercepting(String LogFileName)
{
bool Flag = false;
TextProcess MyLog = new TextProcess();
String Temp = String.Empty;
for (int i = 1; i <= 4;i++)
{
Temp = MyLog.TextRead(KeyValue[i]);
if (Temp == null)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(KeyValue[i]+" Data File Read Information Is null!!");
Console.ForegroundColor = ConsoleColor.White;
Flag = false;
return false;
}
KeyValue[i] = Temp.Trim();
}
for (int i = 5; i <= 14; i++)
{
if (i ==5)
{
Temp = MyLog.StringSplit(LogFileName, KeyValue[i], 1);
}
if (i >5 &&i<=7)
{
Temp = MyLog.StringSplit(LogFileName, KeyValue[i], 3);
}
else if (i >= 8)
{
Temp = MyLog.StringSplit(LogFileName, KeyValue[i], 2);
}
if (Temp == null)
{
Flag = false;
return Flag;
}
KeyValue[i] = Temp;
}
Flag = true;
return Flag;
}
}

public class TextProcess:Program{
public TextProcess() { }
public String StringSplit(String TextFileName, String FindStr,int MethodItem)
{
try
{
FileStream fs = new FileStream(TextFileName,FileMode.Open,FileAccess.Read);
StreamReader sr = new StreamReader(fs,Encoding.Default);
String Temp = String.Empty;
while ((Temp = sr.ReadLine().Trim()) != null)
{
if (Temp.Length > 15)
{
if (MethodItem == 1)
{
String[] Array = Temp.Split(new String[] { ":" }, StringSplitOptions.RemoveEmptyEntries);
if (Array[0].Trim() == FindStr)
return Array[1].Trim();
}
else if (MethodItem == 2)
{
String[] Array = Temp.Split(new String[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (Array[0].Trim() == FindStr.Trim())
return Array[2].Trim();
}
else if (MethodItem == 3)
{
String[] Array = Temp.Split(new String[] { "time:" }, StringSplitOptions.RemoveEmptyEntries);
if (Array[0].Trim() == FindStr)
return Array[1].Trim();
}
}

}
sr.Close();
fs.Close();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(TextFileName + " Log File Read Or String Intercepting Error!");
Console.WriteLine(ex.ToString());
Console.ForegroundColor = ConsoleColor.White;
Environment.Exit(1);
}
return null;
}

public String TextRead(String TextFileName)//读取文本中的字符串
{
try
{
FileStream fs = new FileStream(TextFileName,FileMode.Open,FileAccess.Read);
StreamReader sr = new StreamReader(fs,Encoding.Default);
String Temp = String.Empty;
Temp = sr.ReadLine();
sr.Close();
fs.Close();
return Temp.Trim();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(TextFileName+" Test Data File Read Or Open Error!");
Console.WriteLine(ex.ToString());
Console.ForegroundColor = ConsoleColor.White;
Environment.Exit(1);
}
return null;
}
}
}

2.SQL SERVER DataBase Create Source Code:

USE MASTER
GO
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME="E_AgeTestData")
DROP DATABASE E_AgeTestData
GO

CREATE DATABASE E_AgeTestData
ON PRIMARY
(
   NAME='E_AgeTestData',
   FILENAME='D:\TESTDATA\E_AgeTestData_data.mdf',
   SIZE=5MB,
   MAXSIZE=500000MB
)
LOG ON
(
   NAME='E_AgeTestData_log',
   FILENAME='D:\TESTDATA\E_AgeTestData_log.ldf',
   SIZE=5MB,
   FILEGROWTH=0
)

USE E_AgeTestData
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='BurnInTest_Data')
DROP TABLE BurnInTest_Data
CREATE TABLE BurnInTest_Data
(
   NO INT IDENTITY(1,1)NOT NULL,
   Client_SN VARCHAR(50) NOT NULL,
   LineBody VARCHAR(12) NOT NULL,
   JobNumber VARCHAR(12) NOT NULL,
   Produnct_Name VARCHAR(50) NOT NULL,
   Mac_Address VARCHAR(13) NOT NULL,
   Test_Start_time VARCHAR(50) NOT NULL,
   Test_Stop_time VARCHAR(50) NOT NULL,
   CPU_TEST_Operations VARCHAR(50) NOT NULL,
   Memory_TEST_Operations VARCHAR(50) NOT NULL,
   _2D_Graphics_TEST_Operations VARCHAR(50) NOT NULL,
   _3D_Graphics_TEST_Operations VARCHAR(50) NOT NULL,
   Sound_TEST_Operations VARCHAR(50) NOT NULL,
   Disk_TEST_Operations VARCHAR(50) NOT NULL,
   Network_TEST_Operations VARCHAR(50) NOT NULL,
   TEST_Result VARCHAR(5) NOT NULL,
   Date_Time VARCHAR(20) NOT NULL
)
GO

USE E_AgeTestData
GO
ALTER TABLE BurnInTest_Data
ADD CONSTRAINT PK_Client_SN PRIMARY KEY(Client_SN),
CONSTRAINT UK_Client_SN UNIQUE(Client_SN),
CONSTRAINT UK_Mac_Address UNIQUE(Mac_Address)
GO

3.Sql Server Create PROC SOURCE CODE:

USE E_AgeTestData
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='usp_Insert_BurnInTest_Data')
DROP PROC usp_Insert_BurnInTest_Data
GO

CREATE PROC usp_Insert_BurnInTest_Data
   @Client_SN VARCHAR(50),
   @LineBody VARCHAR(12),
   @JobNumber VARCHAR(12),
   @Produnct_Name VARCHAR(50),
   @Mac_Address VARCHAR(13),
   @Test_Start_time VARCHAR(50),
   @Test_Stop_time VARCHAR(50),
   @CPU_TEST_Operations VARCHAR(50),
   @Memory_TEST_Operations VARCHAR(50),
   @_2D_Graphics_TEST_Operations VARCHAR(50),
   @_3D_Graphics_TEST_Operations VARCHAR(50),
   @Sound_TEST_Operations VARCHAR(50),
   @Disk_TEST_Operations VARCHAR(50),
   @Network_TEST_Operations VARCHAR(50),
   @TEST_Result VARCHAR(5),
   @Date_Time VARCHAR(20),
   @rs int output
AS
   INSERT INTO BurnInTest_Data(Client_SN,LineBody,JobNumber,Produnct_Name,Mac_Address,Test_Start_time,
                               Test_Stop_time,CPU_TEST_Operations,Memory_TEST_Operations,_2D_Graphics_TEST_Operations,
                               _3D_Graphics_TEST_Operations,Sound_TEST_Operations,Disk_TEST_Operations,Network_TEST_Operations,
                               TEST_Result,Date_Time) VALUES(@Client_SN,@LineBody,@JobNumber,@Produnct_Name,
                               @Mac_Address,@Test_Start_time,@Test_Stop_time,@CPU_TEST_Operations,@Memory_TEST_Operations,
                               @_2D_Graphics_TEST_Operations,@_3D_Graphics_TEST_Operations,@Sound_TEST_Operations,
                               @Disk_TEST_Operations,@Network_TEST_Operations,@TEST_Result,@Date_Time)
   IF @@ERROR>0
   BEGIN
       SET @rs=1
   END
   ELSE
   BEGIN
       SET @rs=0
   END
   RETURN @rs
GO