1.sql文件的格式:
localhost,checkdb,root,wujin
set names gbk;
GO
use CheckDB;
GO
create table if not exists DayCheck
(
编号 int(5) auto_increment not null primary key,
机构名称 nvarchar(20),
人员编号 nvarchar(20),
姓名 nvarchar(20),
刷卡日期 nvarchar(20),
时间 nvarchar(20)
);
GO
create table if not exists DayDate
(
daytime nvarchar(20)
);
GO
2.将.sql文件写入数组中:
public ArrayList GetSqlFile(string varFileName)
{
ArrayList alSql = new ArrayList();
if (!File.Exists(varFileName))
{
return alSql;
}
StreamReader rs = new StreamReader(varFileName, System.Text.Encoding.Default);//注意编码
string commandText = "";
string varLine = "";
if (rs.Peek() > -1)
{
varLine = rs.ReadLine();
mArrConnect = varLine.Split(',');
}
while (rs.Peek() > -1)
{
varLine = rs.ReadLine();
if (varLine == "")
{
continue;
}
if (varLine != "GO" && varLine != "go")
{
commandText += varLine;
}
else
{
alSql.Add(commandText);
commandText = "";
}
}
rs.Close();
return alSql;
}
3.执行sql语句:
public void ExecuteCommand(ArrayList varSqlList)
{
MySQLConnection MyConnection = new MySQLConnection(new MySQLConnectionString(mArrConnect[0],mArrConnect[1], mArrConnect[2],mArrConnect[3]).AsString);
MyConnection.Open();
DbTransaction varTrans = MyConnection.BeginTransaction();
try
{
foreach (string varcommandText in varSqlList)
{
MySQLCommand command = new MySQLCommand();
command.Connection = MyConnection;
command.CommandText = varcommandText;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
varTrans.Rollback();
throw ex;
}
finally
{
MyConnection.Close();
}
}
}