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
