dotnet下生成简单sql语句

来源：互联网发布：天涯明月刀男脸型数据编辑：程序博客网时间：2024/06/05 20:18

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 d.name=@tablename --如果只查询指定表,加上此条件
order by a.id,a.colorder
GO

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