dotnet下生成简单sql语句

static public void insert_sql(string tablename,Hashtable param_employeefield,string connstring)
  {
   //***tem.Web.HttpContext.Current.Response.Write("xxx");
   Hashtable ht_field=new Hashtable();
   ht_field=getfieldtype(tablename,connstring); //表的字段
   string field_value;
   string field_type;//字段类型
   string str_sql_fieldname="insert into " + "tablename("; //插入语句
   string str_sql_fieldvalue=" values(";
   string str_sql;
   foreach(object obj_param in param_employeefield)
   {
    field_type=ht_field[ob***aram.ToString()].ToString();//获取 int型 or varchar型等等
    field_value=param_employeefield[obj_param].ToString();
    str_sql_fieldname+=param_employeefield[obj_param].ToString()+",";
    str_sql_fieldvalue+=judgetype(field_type,field_value)+",";
   }

   st***ql_fieldname=str_sql_fieldname.Substring(1,st***ql_fieldname.Length)+")";
   st***ql_fieldvalue=str_sql_fieldvalue.Substring(1,st***ql_fieldvalue.Length)+")";
   str_sql=str_sql_fieldname+str_sql_fieldvalue;
   ns***ore.SqlHelper.ExecuteNonQuery(connstring,Co***ndType.Text,str_sql);
  }

  static public void update_sql(string tablename,Hashtable param_employeefield,string connstring)
  {
   Hashtable ht_field=new Hashtable();
   ht_field=getfieldtype(tablename,connstring);
   string field_value;
   string field_type;
   StringBuilder str_sql  = new StringBuilder();  
   st***ql.Append("update " + "tablename set ");
   string sql1;
   foreach(object obj_param in param_employeefield)
   {
    field_type=ht_field[ob***aram.ToString()].ToString();
    field_value=param_employeefield[obj_param].ToString();
    st***ql.Append(param_employeefield[obj_param].ToString()+"="+judgetype(field_type,field_value)+",");
   }
   sq***st***ql.ToString().Substring(1,st***ql.ToString().Length-1)+" where";
   ns***ore.SqlHelper.ExecuteNonQuery(connstring,Co***ndType.Text,sql1);
  }

  static protected string judgetype(string field_type,string field_value)
  {
   string str_value;
   switch(field_type)
   {
    case "int":     str_value=field_value;
     break;
    case "varchar": str_value="'"+field_value+"'";
     break;
    case "ntext":   str_value="'"+field_value+"'";
     break;
    case "datetime":str_value="'"+field_value+"'";
     break;
    case "tinyint": str_value=field_value;
     break;
    case "smallint": str_value=field_value;
     break;
   }
   return(field_type);
  }

  static protected Hashtable getfieldtype(string tablename,string connstring)
  {
   DataSet ds = new DataSet();
   Hashtable ht_field=new Hashtable();
   SqlParameter[] paramsToStore = new SqlParameter[1];
   paramsToStore[0] = new SqlParameter("@tablename", Sq***Type.NVarChar);
   paramsToStore[0].D***ction=ParameterDirection.Input;
   paramsToStore[0].Value=tablename;
   
   ds***n.core.SqlHelper.ExecuteDataset(connstring,Co***ndType.StoredProcedure,"main_searchtable",paramsToStore);
   DataTable tb***s.Tables[0];
   foreach(DataRow row in tbl.Rows)
   {
   
    ht***eld.Add(row["字段名"].ToString(),row["类型"].ToString());
    //***tem.Web.HttpContext.Current.Response.Write(row["字段名"].ToString());
   }
   return(ht_field);
  }

main_searchtable 存储过程是
CREATE PROCEDURE main_searchtable
@tablename nvarchar(50)
AS 
 
SELECT  
 表名=case when a.***order=1 then d.name else '' end, 
 表说明=case when a.***order=1 then isnull(f.value,'') else '' end, 
 字段序号=a***lorder, 
 字段名=a.name, 
 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 
 类型=b***me ;
 
  
FROM syscolumns a 
 left join systypes b on a.***pe=b.xusertype ;
 inner join sysobjects d on a.***d.id ; and d.xtype='U' and  d.name<>'dtproperties' 
 
 left join sysproperties f on d.***f.id and f.***llid=0 ;
 
where d.***e=@tablename">d.***e=@tablename    --如果只查询指定表,加上此条件 
order by a.id,a.***order
GO

http://liuxiaoyi666.cnblogs.com/archive/2006/05/28/411082.html