下面是数据文本文件的格式
“#”号是注释
“T:”是加入
“D:”是删除表里面的数据
“C:”是检查数据
数据与数据之间用TAB隔开(可以从企业管理器直接把数据拷贝出来作为数据源)
加入数据的事列如下:
#插入用户表
# null为插入空
T:Users
John <TAB>John chan
Cake<TAB>Cake.lu
jeff<TAB>jeff.hu
# CheckTable's Data
#null为检查是否为空,skip掉过不检查,notnull检查他是否不为空
C:Users
John <TAB>John chan
Cake<TAB>Cake.lu
jeff<TAB>jeff.hu
#Delete All Data
D:TAbleName
调用代码:
参数1:文本文件的路径
参数2:是Helper里面的两个常量(其实可以是个Enum,不过写的时候没有注意)
Helper.ExecuteAction("Data.txt",Helper.INSERT);
参数3:是指定对那个对象操作(T:D:C:后面的名称,如T:Users,那么就填Users)
Helper.ExecuteACtion("Data.txt",Helper.Insert,"Users")
代码如下:
using System;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using NUnit.Framework;
namespace TestCore
{
public class Helper
{
public const string DELETE="D";
public const string INSERT="T";
public const string CHECK="C";
public const string ANNOTATE="#";
public static string ConnectionString
{
//这里要写返回的连接字符串
get{return null;}
}
public static void ExecuteAction(string path,string action)
{
ExecuteAction(path,action,null);
}
public static void ExecuteAction(string path,string action,string inputTable)
{
Console.WriteLine("----------------"+action+" action is Start--------------------");
StreamReader reader=new StreamReader(path);
string line;
string table=null;
try
{
while(true)
{
line =reader.ReadLine();
if(line==null)
break;
if(line.Length==0)
continue;
string Key=line.Substring(0,1);
if(Key==action)
{
table=line.Substring(2);
if(action==DELETE)
{
if(CheckTable(inputTable,table))
{
Delete(table);
}
table=null;
}
else
{
continue;
}
}
else if(table!=null)
{
switch(action)
{
case INSERT:
if(CheckTable(inputTable,table))
{
string[] datas=line.Split('\t');
AddData(datas,table);
}
break;
case CHECK:
if(CheckTable(inputTable,table))
{
string[] checkDatas=line.Split('\t');
Check(checkDatas,table);
}
break;
}
table =null;
}
else
{
continue;
}
}
}
finally
{
Console.WriteLine("----------------"+action+" action is End--------------------");
reader.Close();
}
}
private static bool CheckTable(string inputTable,string SettingTable)
{
if(inputTable==null)
return true;
return (inputTable==SettingTable);
}
private static void Delete(string table)
{
string sql=" delete from "+table;
Console.WriteLine(sql);
try
{
SqlHelper.ExecuteNonQuery(ConnectionString,CommandType.Text,sql);
}
catch(Exception e)
{
throw new ApplicationException("删除错误:Sql是"+sql,e);
}
}
private static void AddData(string[] data,string table)
{
SqlConnection conn=new SqlConnection(ConnectionString);
string sql="insert into "+table+" values ";
string temp="('"+data[0]+"'";
for(int i=1;i<data.Length;i++)
{
if(data[i]!="null")
temp+=",'"+data[i]+"'";
else
temp+=",null";
}
sql+=temp+")";
conn.Open();
try
{
Console.WriteLine(sql);
SqlCommand comm =new SqlCommand(sql,conn);
comm.ExecuteNonQuery();
}
catch(Exception e)
{
throw new ApplicationException(table+":"+sql,e);
}
finally
{
conn.Close();
}
}
private static void Check(string[] data,string table)
{
string sql="select * from "+table;
Console.WriteLine(sql);
DataSet ds = SqlHelper.ExecuteDataset(Helper.ConnectionString,CommandType.Text,sql);
DataTable dt=ds.Tables[0];
if(dt.Rows.Count==0)
throw new ArgumentOutOfRangeException( sql+" 返回结构为空");
foreach(DataRow dr in dt.Rows)
{
for(int i=0;i<data.Length;i++)
{
if(data[i].ToLower()=="skip")
continue;
if(data[i].ToLower()=="null")
{
Assert.IsTrue(dr[i].Equals(DBNull.Value),"expect is null ,but it's not null actualy");
continue;
}
if(data[i].ToLower()=="notnull")
{
Assert.IsFalse(dr[i].Equals(DBNull.Value),"expect isn't null but it's null actualy");
continue;
}
if(data[i].Length>=4 && data[i].Substring(0,4).ToLower()=="not:")
{
string txtValue=data[i].Substring(4).ToLower().Trim();
string dbValue=dr[i].ToString().ToLower().Trim();
Assert.IsTrue(txtValue!=dbValue,"db value is the same as data in text file,text file value is "+txtValue);
continue;
}
if(dr[i].ToString().Length!=0 || dr[i].Equals(DBNull.Value))
{
Assert.IsTrue(dr[i].ToString()==data[i],table+":Check Value Is "+data[i]+",but DB is "+dr[i]);
continue;
}
}
}
}
}
}