拼接sql語句
很早以前的一個方法，對于一些比較簡單的小項目經(jīng)常使用，自己在這里有進行了下完善和修改，感覺這個方法不錯，不用寫那些煩人的Insert和Update語句，尤其是字段特別多的時候，寫起來很費時，可以省寫很多代碼，發(fā)出來大家看看，有什么不好的地方請各位大蝦們指教。
假如我們要對表Users表進行新增和編輯
首先定義一個實體User實體類:
![]()
/// <summary>
![]()
/// 實體類Users
![]()
/// </summary>
![]()
public class Users
![]()
{
![]()
public Users()
![]()
{ }
![]()
![]()
private int _id;
![]()
private string _username;
![]()
private int _usercode;
![]()
private string _sex;
![]()
private int _age;
![]()
![]()
/// <summary>
![]()
/// 系統(tǒng)ID，默認(rèn)自增
![]()
/// </summary>
![]()
public int ID
![]()
{
![]()
set { _id = value; }
![]()
get { return _id; }
![]()
}
![]()
/// <summary>
![]()
/// 用戶名
![]()
/// </summary>
![]()
public string UserName
![]()
{
![]()
set { _username = value; }
![]()
get { return _username; }
![]()
}
![]()
/// <summary>
![]()
/// 用戶編號
![]()
/// </summary>
![]()
public int UserCode
![]()
{
![]()
set { _usercode = value; }
![]()
get { return _usercode; }
![]()
}
![]()
/// <summary>
![]()
/// 性別
![]()
/// </summary>
![]()
public string Sex
![]()
{
![]()
set { _sex = value; }
![]()
get { return _sex; }
![]()
}
![]()
/// <summary>
![]()
/// 年齡
![]()
/// </summary>
![]()
public int Age
![]()
{
![]()
set { _age = value; }
![]()
get { return _age; }
![]()
}
![]()
}
存儲sql語句字段名、字段值和字段類型的類：
![]()
public class SqlText
![]()
{
![]()
private string _ColumnName;
![]()
private string _ColumnValue;
![]()
private string _ColumnType;
![]()
![]()
public static string strType = "String";
![]()
public static string intType = "Int";
![]()
![]()
![]()
public SqlText(string ColumnName, string ColumnValue, string ColumnType)
![]()
{
![]()
this._ColumnName = ColumnName;
![]()
this._ColumnValue = ColumnValue;
![]()
this._ColumnType = ColumnType;
![]()
}
![]()
![]()
public string ColumnName
![]()
{
![]()
get
![]()
{
![]()
return _ColumnName;
![]()
}
![]()
}
![]()
![]()
public string ColumnValue
![]()
{
![]()
get
![]()
{
![]()
return _ColumnValue;
![]()
}
![]()
}
![]()
![]()
public string ColumnType
![]()
{
![]()
get
![]()
{
![]()
return _ColumnType;
![]()
}
![]()
}
![]()
}
生成sql語句的類：![]()
public class CreateSql
![]()
{
![]()
/// <summary>
![]()
/// 插入sql語句
![]()
/// </summary>
![]()
/// <param name="list">存放數(shù)據(jù)對象</param>
![]()
/// <param name="TableName">插入表名稱</param>
![]()
public static void Insert(List<SqlText> list, string TableName)
![]()
{
![]()
string sql = CreateInsert(list, TableName);
![]()
}
![]()
![]()
/// <summary>
![]()
/// 修改sql語句
![]()
/// </summary>
![]()
/// <param name="list">存放數(shù)據(jù)對象</param>
![]()
/// <param name="TableName">插入表名稱</param>
![]()
/// <param name="strWhere">輸入條件，例如：ID=1</param>
![]()
public static void Update(List<SqlText> list, string TableName, string strWhere)
![]()
{
![]()
string sql = CreateUpdate(list, TableName, strWhere);
![]()
}
![]()
![]()
public static string CreateInsert(List<SqlText> list, string TableName)
![]()
{
![]()
StringBuilder sb = new StringBuilder();
![]()
StringBuilder sbStart = new StringBuilder();
![]()
StringBuilder sbEnd = new StringBuilder();
![]()
sbStart.Append("INSERT INTO " + TableName + " (");
![]()
sbEnd.Append(" VALUES (");
![]()
for (int i = 0; i < list.Count; i++)
![]()
{
![]()
if (i == 0)
![]()
{
![]()
sbStart.Append(list[i].ColumnName);
![]()
if (list[i].ColumnType == SqlText.strType)
![]()
{
![]()
sbEnd.Append("'" + list[i].ColumnValue + "'");
![]()
}
![]()
else
![]()
{
![]()
sbEnd.Append(list[i].ColumnValue);
![]()
}
![]()
}
![]()
else
![]()
{
![]()
sbStart.Append("," + list[i].ColumnName);
![]()
if (list[i].ColumnType == SqlText.strType)
![]()
{
![]()
sbEnd.Append(",'" + list[i].ColumnValue + "'");
![]()
}
![]()
else
![]()
{
![]()
sbEnd.Append("," + list[i].ColumnValue);
![]()
}
![]()
}
![]()
}
![]()
sbStart.Append(")");
![]()
sbEnd.Append(")");
![]()
![]()
sb.Append(sbStart.ToString() + sbEnd.ToString());
![]()
return sb.ToString();
![]()
}
![]()
![]()
public static string CreateUpdate(List<SqlText> list, string TableName, string strWhere)
![]()
{
![]()
StringBuilder sb = new StringBuilder();
![]()
sb.Append("UPDATE " + TableName + " SET ");
![]()
for (int i = 0; i < list.Count; i++)
![]()
{
![]()
if (i == 0)
![]()
{
![]()
sb.Append(list[i].ColumnName + " = ");
![]()
}
![]()
else
![]()
{
![]()
sb.Append("," + list[i].ColumnName + " = ");
![]()
}
![]()
if (list[i].ColumnType == SqlText.strType)
![]()
{
![]()
sb.Append("'" + list[i].ColumnValue + "'");
![]()
}
![]()
else
![]()
{
![]()
sb.Append(list[i].ColumnValue);
![]()
}
![]()
}
![]()
sb.Append(" WHERE " + strWhere);
![]()
return sb.ToString();
![]()
}
![]()
}
具體實現(xiàn)方法：
![]()
class Program
![]()
{
![]()
static void Main(string[] args)
![]()
{
![]()
//如果是新增一位人員信息
![]()
string type = "ADD";
![]()
//如果是編輯一位人員信息
![]()
//string type = "ADD";
![]()
![]()
Users user = new Users();
![]()
user.UserName = "OR";
![]()
user.UserCode = 1;
![]()
user.Sex = "男";
![]()
user.Age = 24;
![]()
![]()
List<SqlText> list = new List<SqlText>();
![]()
list.Add(new SqlText("UserName", user.UserName.ToString(), SqlText.strType));
![]()
list.Add(new SqlText("UserCode", user.UserCode.ToString(), SqlText.intType));
![]()
list.Add(new SqlText("Sex", user.Sex.ToString(), SqlText.strType));
![]()
list.Add(new SqlText("Age", user.Age.ToString(), SqlText.intType));
![]()
try
![]()
{
![]()
if (type == "ADD") //新增
![]()
{
![]()
CreateSql.Insert(list, "Users");
![]()
list.Clear();
![]()
}
![]()
else if (type == "EDIT") //修改
![]()
{
![]()
CreateSql.Update(list, "Users", "ID=" + type);
![]()
list.Clear();
![]()
}
![]()
}
![]()
catch
![]()
{
![]()
throw;
![]()
}
![]()
}
![]()
}
假如我們要對表Users表進行新增和編輯
首先定義一個實體User實體類:
/// <summary>
/// 實體類Users
/// </summary>
public class Users {
public Users()
{ }
private int _id; private string _username; private int _usercode; private string _sex; private int _age; /// <summary> /// 系統(tǒng)ID，默認(rèn)自增
/// </summary> public int ID { set { _id = value; } get { return _id; } } /// <summary> /// 用戶名 /// </summary> public string UserName { set { _username = value; } get { return _username; } } /// <summary> /// 用戶編號 /// </summary> public int UserCode { set { _usercode = value; } get { return _usercode; } } /// <summary> /// 性別 /// </summary> public string Sex { set { _sex = value; } get { return _sex; } } /// <summary> /// 年齡 /// </summary> public int Age { set { _age = value; } get { return _age; } } }存儲sql語句字段名、字段值和字段類型的類：
public class SqlText { private string _ColumnName; private string _ColumnValue; private string _ColumnType; public static string strType = "String"; public static string intType = "Int"; public SqlText(string ColumnName, string ColumnValue, string ColumnType) { this._ColumnName = ColumnName; this._ColumnValue = ColumnValue; this._ColumnType = ColumnType; } public string ColumnName { get { return _ColumnName; } } public string ColumnValue { get { return _ColumnValue; } } public string ColumnType { get { return _ColumnType; } } }生成sql語句的類：
public class CreateSql { /// <summary> /// 插入sql語句 /// </summary> /// <param name="list">存放數(shù)據(jù)對象</param> /// <param name="TableName">插入表名稱</param> public static void Insert(List<SqlText> list, string TableName) { string sql = CreateInsert(list, TableName); } /// <summary> /// 修改sql語句 /// </summary> /// <param name="list">存放數(shù)據(jù)對象</param> /// <param name="TableName">插入表名稱</param> /// <param name="strWhere">輸入條件，例如：ID=1</param> public static void Update(List<SqlText> list, string TableName, string strWhere) { string sql = CreateUpdate(list, TableName, strWhere); } public static string CreateInsert(List<SqlText> list, string TableName) { StringBuilder sb = new StringBuilder(); StringBuilder sbStart = new StringBuilder(); StringBuilder sbEnd = new StringBuilder(); sbStart.Append("INSERT INTO " + TableName + " ("); sbEnd.Append(" VALUES ("); for (int i = 0; i < list.Count; i++) { if (i == 0) { sbStart.Append(list[i].ColumnName); if (list[i].ColumnType == SqlText.strType) { sbEnd.Append("'" + list[i].ColumnValue + "'"); } else { sbEnd.Append(list[i].ColumnValue); } } else { sbStart.Append("," + list[i].ColumnName); if (list[i].ColumnType == SqlText.strType) { sbEnd.Append(",'" + list[i].ColumnValue + "'"); } else { sbEnd.Append("," + list[i].ColumnValue); } } } sbStart.Append(")"); sbEnd.Append(")"); sb.Append(sbStart.ToString() + sbEnd.ToString()); return sb.ToString(); } public static string CreateUpdate(List<SqlText> list, string TableName, string strWhere) { StringBuilder sb = new StringBuilder(); sb.Append("UPDATE " + TableName + " SET "); for (int i = 0; i < list.Count; i++) { if (i == 0) { sb.Append(list[i].ColumnName + " = "); } else { sb.Append("," + list[i].ColumnName + " = "); } if (list[i].ColumnType == SqlText.strType) { sb.Append("'" + list[i].ColumnValue + "'"); } else { sb.Append(list[i].ColumnValue); } } sb.Append(" WHERE " + strWhere); return sb.ToString(); } }具體實現(xiàn)方法：
class Program { static void Main(string[] args) { //如果是新增一位人員信息 string type = "ADD"; //如果是編輯一位人員信息 //string type = "ADD"; Users user = new Users(); user.UserName = "OR"; user.UserCode = 1; user.Sex = "男"; user.Age = 24; List<SqlText> list = new List<SqlText>(); list.Add(new SqlText("UserName", user.UserName.ToString(), SqlText.strType)); list.Add(new SqlText("UserCode", user.UserCode.ToString(), SqlText.intType)); list.Add(new SqlText("Sex", user.Sex.ToString(), SqlText.strType)); list.Add(new SqlText("Age", user.Age.ToString(), SqlText.intType)); try { if (type == "ADD") //新增 { CreateSql.Insert(list, "Users"); list.Clear(); } else if (type == "EDIT") //修改 { CreateSql.Update(list, "Users", "ID=" + type); list.Clear(); } } catch { throw; } } } 
浙公網(wǎng)安備 33010602011771號