/// <summary>
/// 利用数组获取sql脚本文件中的sql语句
/// </summary>
/// <param name="targetdir">路径</param>
/// <param name="dbname">数据库名</param>
/// <returns></returns>
public static ArrayList Getarraylist(string targetdir, string dbname)
{
ArrayList sqllist = new ArrayList();
try
{
System.IO.FileInfo FileInfo = new System.IO.FileInfo(targetdir + "DATABASE.sql");
string path = System.IO.Path.Combine(targetdir, "DATABASE.sql");
string varLine = "";
StreamReader sr = new StreamReader(path,System.Text.Encoding.Default);
while (sr.Peek() > -1)
{
varLine = sr.ReadLine();
varLine = varLine.Replace("[kms]", "[" + dbname + "]");
if (varLine == "")
{
continue;
}
if (varLine != "GO" && varLine != "go" && varLine.Substring(0, 1) != "/")
{
commandText += varLine;
commandText += "\r\n";
}
else
{
sqllist.Add(commandText);
commandText = "";
}
}
//string line = sr.ReadToEnd();
sr.Close();
//line = line.Replace("[kms]", "[" + dbname + "]");
return sqllist;
}
catch (Exception e)
{
throw new InstallException(e.Message);
}
}
//将获取到的数据传入这个函数就可以执行sql语句
public static void ExcuteSql(SqlConnection connectstring, ArrayList sql)
{
try
{
SqlTransaction varTrans = connectstring.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connectstring;
cmd.Transaction = varTrans;
foreach (string sqlstring in sql)
{
if (sqlstring != null)
{
cmd.CommandText = sqlstring;
cmd.ExecuteNonQuery();
}
}
varTrans.Commit();
}
catch (Exception e)
{
throw new InstallException(e.Message);
}
}
}
/// <summary>
/// 利用数组获取sql脚本文件中的sql语句
/// </summary>
/// <param name="targetdir">路径</param>
/// <param name="dbname">数据库名</param>
/// <returns></returns>
public static ArrayList Getarraylist(string targetdir, string dbname) //静态函数，输入两个字符串路径和数据库名，返回一个ArrayList里面是sql语句
{
ArrayList sqllist = new ArrayList();//新建一个ArrayList待用，这里用List<string>会更好，避免用弱类型
try
{
System.IO.FileInfo FileInfo = new System.IO.FileInfo(targetdir + "DATABASE.sql"); //文件信息对象
string path = System.IO.Path.Combine(targetdir, "DATABASE.sql"); //合并目录，比直接字符串相加要保险，否则容易多出斜杠造成非法路径
string varLine = "";
StreamReader sr = new StreamReader(path,System.Text.Encoding.Default); //数据流读取对象
while (sr.Peek() > -1)//判断是否读到文件的结尾
{
varLine = sr.ReadLine();//按字符串格式读取一行
varLine = varLine.Replace("[kms]", "[" + dbname + "]");//替换字符串，将[kms]括号里的部分替换成数据库名称
if (varLine == "")//如果读出来是空行跳过，继续读下一行。这应该在替换之前写的
{
continue;
}
if (varLine != "GO" && varLine != "go" && varLine.Substring(0, 1) != "/")//判断如果不是GO或者/
{
commandText += varLine;//追加进一个字符串commandText里面
commandText += "\r\n";
}
else //如果是GO或者/
{
sqllist.Add(commandText); //把拼接好的commandText放到sqllist里
commandText = "";//清空，以便继续往里拼接新的sql语句
}
}
//string line = sr.ReadToEnd();
sr.Close();//都结束了close掉，但是这种用法不好，如果报错进入catch则close不会执行，造成文件打开不关闭，应该使用using关键字
//line = line.Replace("[kms]", "[" + dbname + "]");
return sqllist;//返回sqllist
}
catch (Exception e)
{
throw new InstallException(e.Message);//如果报错了则抛出错误，多此一举，这加try加了个寂寞，要不想处理干脆别加try
}
}
//将获取到的数据传入这个函数就可以执行sql语句
public static void ExcuteSql(SqlConnection connectstring, ArrayList sql)//传入数据库连接对象和sql语句列表，并执行
{
try
{
SqlTransaction varTrans = connectstring.BeginTransaction();//开启事务
SqlCommand cmd = new SqlCommand();//新建命令
cmd.Connection = connectstring;//数据库连接赋值
cmd.Transaction = varTrans;//命令事务赋值
foreach (string sqlstring in sql)//循环，遍历列表
{
if (sqlstring != null)//多此一举，前面已经保证了列表里不是空的
{
cmd.CommandText = sqlstring;//sql命令赋值
cmd.ExecuteNonQuery();//sql指令执行
}
}
varTrans.Commit();//如果全部执行成功不报错则提交
}
catch (Exception e)
{
throw new InstallException(e.Message);//抛出异常，这里缺少回滚的操作
}
}
}