1、把长的SQL语句保存成单独的文件,如把下面的SQL语句保存成insert_users.sql文件
insert into users(UserName,Sex,Age,Email,Address)
values('@param000','@param001','@param002','@param003','@param004')
@parm***:代表插入SQL语句的参数
2、以下两个函数可能在你的项目中经常使用,所以建议写到公共类里面,在这里我定义公共类为DataBase:
public static string readSQL(string filePath, string[] param)
{
FileStream fs = new FileStream(filePath, FileMode.Open);
StreamReader sr = new StreamReader(fs);
string strSql = sr.ReadToEnd();
sr.Close();
fs.Close();
return ArrayReplace(strSql, param);
}
public static string ArrayReplace(string src, string[] param)
{
int ArrayMax = param.Length;
for (int i = 0; i < ArrayMax; i++)
{
string a = i.ToString();
string rep = "@param" + a.PadLeft(3, '0');
src = src.Replace(rep, param[i]);
}
return src;
}
3、引用公共类的以上两个方法,进行SQL语句中参数的值替换,使用如下(常写在.cs文件中)
public string getSQL()
{
string sqlFileName;
string strResult;
string[] param = new string[5];
try
{
sqlFileName = "users_insert.sql";
strResult = "";
param[0] = txtName.Text.Trim();
param[1] = ddlSex.SelectedIndex.ToString();
param[2] = txtAge.Text.Trim();
param[3] = txtEmail.Text.Trim();
param[4] = txtAddress.Text.Trim();
strResult = DataBase.readSQL(Server.MapPath("sql/")+sqlFileName, param);
}
catch (Exception exGetSql)
{
throw new Exception(exGetSql.Message);
}
return strResult;
}
这样返回的结果形式如下:
insert into users(UserName,Sex,Age,Email,Address)
values('jack','男','24','jack@jack.com','深圳市罗湖区')
这样的话,当你修改sql语句时只需要修改sql文件(当然前提是参数的个数一定要配对哟),而无需进行程序的再编译。:)