dotnet下生成简单sql语句

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航