首页 > 编程 > .NET > 正文

dotnet下生成简单sql语句

2024-07-21 02:29:04
字体:
来源:转载
供稿:网友

static public void insert_sql(string tablename,hashtable param_employeefield,string connstring)
  {
   //system.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[obj_param.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)+",";
   }

   str_sql_fieldname=str_sql_fieldname.substring(1,str_sql_fieldname.length)+")";
   str_sql_fieldvalue=str_sql_fieldvalue.substring(1,str_sql_fieldvalue.length)+")";
   str_sql=str_sql_fieldname+str_sql_fieldvalue;
   nsn.core.sqlhelper.executenonquery(connstring,commandtype.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();  
   str_sql.append("update " + "tablename set ");
   string sql1;
   foreach(object obj_param in param_employeefield)
   {
    field_type=ht_field[obj_param.tostring()].tostring();
    field_value=param_employeefield[obj_param].tostring();
    str_sql.append(param_employeefield[obj_param].tostring()+"="+judgetype(field_type,field_value)+",");
   }
   sql1=str_sql.tostring().substring(1,str_sql.tostring().length-1)+" where";
   nsn.core.sqlhelper.executenonquery(connstring,commandtype.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", sqldbtype.nvarchar);
   paramstostore[0].direction=parameterdirection.input;
   paramstostore[0].value=tablename;
   
   ds=nsn.core.sqlhelper.executedataset(connstring,commandtype.storedprocedure,"main_searchtable",paramstostore);
   datatable tbl=ds.tables[0];
   foreach(datarow row in tbl.rows)
   {
   
    ht_field.add(row["字段名"].tostring(),row["类型"].tostring());
    //system.web.httpcontext.current.response.write(row["字段名"].tostring());
   }
   return(ht_field);
  }

main_searchtable 存储过程是
create procedure main_searchtable
@tablename nvarchar(50)
as 
 
select  
 表名=case when a.colorder=1 then d.name else '' end, 
 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end, 
 字段序号=a.colorder, 
 字段名=a.name, 
 标识=case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' end, 
 类型=b.name 
 
  
from syscolumns a 
 left join systypes b on a.xtype=b.xusertype 
 inner join sysobjects d on a.id=d.id  and d.xtype='u' and  d.name<>'dtproperties' 
 
 left join sysproperties f on d.id=f.id and f.smallid=0 
 
where [email protected]    --如果只查询指定表,加上此条件 
order by a.id,a.colorder
go

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表