拼接sql语句

     很早以前的一个方法,对于一些比较简单的小项目经常使用,自己在这里有进行了下完善和修改,感觉这个方法不错,不用写那些烦人的Insert和Update语句,尤其是字段特别多的时候,写起来很费时,可以省写很多代码,发出来大家看看,有什么不好的地方请各位大虾们指教。
     假如我们要对表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>
        
/// 系统ID,默认自增
        
/// </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">存放数据对象</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">存放数据对象</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();
        }

    }

    具体实现方法:
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;
            }

        }

    }





posted @ 2008-03-06 15:34  love .net FrameWork  阅读(7304)  评论(5编辑  收藏  举报