DataHelper
using System; using System.Data; using System.Data.SqlClient; using System.Text; namespace Document
{ 
/**//// <summary>
/// Summary description for DataHelper.
/// </summary> public class DataHelper { public DataHelper() { // // TODO: Add constructor logic here // } public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; GetDataSet#region GetDataSet public static DataSet GetDataSet(string sql) { SqlDataAdapter sda =new SqlDataAdapter(sql,ConnectionString); DataSet ds=new DataSet(); sda.Fill(ds); return ds; } #endregion ExecCommand#region ExecCommand public static int ExecCommand(SqlCommand sqlcom) { SqlConnection conn=new SqlConnection(ConnectionString); sqlcom.Connection =conn; conn.Open(); try { int rtn=sqlcom.ExecuteNonQuery(); return rtn; } catch(Exception ex) { throw ex; } finally { conn.Close(); } return 0; } public static int ExecCommand(string sql) { if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1); SqlCommand sqlcom=new SqlCommand(sql); return ExecCommand(sqlcom); } #endregion ExecuteScalar#region ExecuteScalar public static object ExecuteScalar(string sql) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); conn.Open(); try { object rtn=sqlcom.ExecuteScalar (); return rtn; } catch(Exception ex) { throw ex; } finally { conn.Close(); } return null; } #endregion ExecSPCommand#region ExecSPCommand public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); sqlcom.CommandType= CommandType.StoredProcedure ; foreach(System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); try { sqlcom.ExecuteNonQuery(); } catch(Exception ex) { string s=ex.Message ; } finally { conn.Close(); } } #endregion ExecSPDataSet#region ExecSPDataSet public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); sqlcom.CommandType= CommandType.StoredProcedure ; foreach(System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); SqlDataAdapter da=new SqlDataAdapter(); da.SelectCommand=sqlcom; DataSet ds=new DataSet(); da.Fill(ds); conn.Close(); return ds; } #endregion DbType#region DbType private static System.Data.DbType GetDbType(Type type) { DbType result = DbType.String; if( type.Equals(typeof(int)) || type.IsEnum) result = DbType.Int32; else if( type.Equals(typeof(long))) result = DbType.Int32; else if( type.Equals(typeof(double)) || type.Equals( typeof(Double))) result = DbType.Decimal; else if( type.Equals(typeof(DateTime))) result = DbType.DateTime; else if( type.Equals(typeof(bool))) result = DbType.Boolean; else if( type.Equals(typeof(string) ) ) result = DbType.String; else if( type.Equals(typeof(decimal))) result = DbType.Decimal; else if( type.Equals(typeof(byte[]))) result = DbType.Binary; else if( type.Equals(typeof(Guid))) result = DbType.Guid; return result; } #endregion UpdateTable#region UpdateTable public static void UpdateTable(DataTable dt,string TableName,string KeyName) { foreach(DataRow dr in dt.Rows) { updateRow(dr,TableName,KeyName); } } #endregion InsertTable#region InsertTable //用于主键是数据库表名+ID类型的 public static void InsertTable(DataTable dt) { string TableName="["+dt.TableName+"]"; string KeyName=dt.TableName+"ID"; foreach(DataRow dr in dt.Rows) { insertRow(dr,TableName,KeyName); } } //用于主键是任意类型的 public static void InsertTable(DataTable dt,string KeyName) { string TableName="["+dt.TableName+"]"; foreach(DataRow dr in dt.Rows) { insertRow(dr,TableName,KeyName); } } #endregion DeleteTable#region DeleteTable public static void DeleteTable(DataTable dt,string KeyName) { string TableName="["+dt.TableName+"]"; foreach(DataRow dr in dt.Rows) { deleteRow(dr,TableName,KeyName); } } #endregion updateRow#region updateRow private static void updateRow(DataRow dr,string TableName,string KeyName) { if (dr[KeyName]==DBNull.Value ) { throw new Exception(KeyName +"的值不能为空"); } if (dr.RowState ==DataRowState.Deleted) { deleteRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Modified ) { midifyRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Added ) { insertRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Unchanged ) { midifyRow(dr,TableName,KeyName); } } #endregion deleteRow#region deleteRow private static void deleteRow(DataRow dr,string TableName,string KeyName) { string sql="Delete {0} where {1} =@{1}"; DataTable dtb=dr.Table ; sql=string.Format(sql,TableName,KeyName); SqlCommand sqlcom=new SqlCommand(sql); System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ KeyName; iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType); iparam.Value = dr[KeyName]; sqlcom.Parameters .Add(iparam); ExecCommand(sqlcom); } #endregion midifyRow#region midifyRow private static void midifyRow(DataRow dr,string TableName,string KeyName) { string UpdateSql = "Update {0} set {1} {2}"; string setSql="{0}= @{0}"; string wherSql=" Where {0}=@{0}"; StringBuilder setSb = new StringBuilder(); SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table; for (int k=0; k<dr.Table.Columns.Count; ++k) { System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters .Add(iparam); if (dtb.Columns[k].ColumnName==KeyName) { wherSql=string.Format(wherSql,KeyName); } else { setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName)); setSb.Append(","); } } string setStr=setSb.ToString(); setStr=setStr.Substring(0,setStr.Length -1); //trim , string sql = string.Format(UpdateSql, TableName, setStr,wherSql); sqlcom.CommandText =sql; try { ExecCommand(sqlcom); } catch(Exception ex) { throw ex; } } #endregion insertRow#region insertRow private static void insertRow(DataRow dr,string TableName,string KeyName) { string InsertSql = "Insert into {0}({1}) values({2})"; SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table ; StringBuilder insertValues = new StringBuilder(); StringBuilder cloumn_list = new StringBuilder(); for (int k=0; k<dr.Table.Columns.Count; ++k) { //just for genentae， if (dtb.Columns[k].ColumnName==KeyName) continue; System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters .Add(iparam); cloumn_list.Append(dtb.Columns[k].ColumnName); insertValues.Append("@"+dtb.Columns[k].ColumnName); cloumn_list.Append(","); insertValues.Append(","); } string cols=cloumn_list.ToString(); cols=cols.Substring(0,cols.Length -1); string values=insertValues.ToString(); values=values.Substring(0,values.Length -1); string sql = string.Format(InsertSql, TableName,cols ,values); sqlcom.CommandText =sql; try { ExecCommand(sqlcom); } catch(Exception ex) { throw ex; } } #endregion }
} 2..调用范例 Insert #region Insert private void InsertUserInfo() { DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserInfoID"; DataRow dr=dt.NewRow(); dr["LoginName"]=this.txtUserName.Value; dr["Pass"]=this.txtPassword.Value; dr["NickName"]=this.txtNickName.Value; dr["UserType"]=1; dr["IsActive"]=false; dr["RegisterDate"]=System.DateTime.Now; dt.Rows.Add(dr); dt.AcceptChanges(); DataHelper.InsertTable(dt,keyname); } #endregion Update #region Update private void UpdateUserInfo(string UserID) { DataSet ds=GetUserOther(UserID); DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserID"; DataRow dr=dt.Rows[0]; dr["LoginName"]=this.txtUserName.Value; dr["Pass"]=this.txtPassword.Value; dr["NickName"]=this.txtNickName.Value; dr["UserType"]=1; dr["IsActive"]=false; dr["RegisterDate"]=System.DateTime.Now; dt.Rows.Add(dr); dt.AcceptChanges(); DataHelper.UpdateTable(dt,dt.TableName,keynanme); } #endregion Delete #region Delete private void DeleteUserInfo(string UserID) { DataSet ds=GetUserOther(UserID); DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserID"; DataHelper.DeleteTable(dt,keyname); } #endregion
发布日期：2021-06-30 19:03:58
浏览次数：3
分类：技术文章
本文共 8320 字，大约阅读时间需要 27 分钟。
1.建一个通用的处理数据的类
using System; using System.Data; using System.Data.SqlClient; using System.Text; namespace Document { /**//// <summary> /// Summary description for DataHelper. /// </summary> public class DataHelper { public DataHelper() { // // TODO: Add constructor logic here // } public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; GetDataSet#region GetDataSet public static DataSet GetDataSet(string sql) { SqlDataAdapter sda =new SqlDataAdapter(sql,ConnectionString); DataSet ds=new DataSet(); sda.Fill(ds); return ds; } #endregion ExecCommand#region ExecCommand public static int ExecCommand(SqlCommand sqlcom) { SqlConnection conn=new SqlConnection(ConnectionString); sqlcom.Connection =conn; conn.Open(); try { int rtn=sqlcom.ExecuteNonQuery(); return rtn; } catch(Exception ex) { throw ex; } finally { conn.Close(); } return 0; } public static int ExecCommand(string sql) { if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1); SqlCommand sqlcom=new SqlCommand(sql); return ExecCommand(sqlcom); } #endregion ExecuteScalar#region ExecuteScalar public static object ExecuteScalar(string sql) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); conn.Open(); try { object rtn=sqlcom.ExecuteScalar (); return rtn; } catch(Exception ex) { throw ex; } finally { conn.Close(); } return null; } #endregion ExecSPCommand#region ExecSPCommand public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); sqlcom.CommandType= CommandType.StoredProcedure ; foreach(System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); try { sqlcom.ExecuteNonQuery(); } catch(Exception ex) { string s=ex.Message ; } finally { conn.Close(); } } #endregion ExecSPDataSet#region ExecSPDataSet public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers) { SqlConnection conn=new SqlConnection(ConnectionString); SqlCommand sqlcom=new SqlCommand(sql,conn); sqlcom.CommandType= CommandType.StoredProcedure ; foreach(System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); SqlDataAdapter da=new SqlDataAdapter(); da.SelectCommand=sqlcom; DataSet ds=new DataSet(); da.Fill(ds); conn.Close(); return ds; } #endregion DbType#region DbType private static System.Data.DbType GetDbType(Type type) { DbType result = DbType.String; if( type.Equals(typeof(int)) || type.IsEnum) result = DbType.Int32; else if( type.Equals(typeof(long))) result = DbType.Int32; else if( type.Equals(typeof(double)) || type.Equals( typeof(Double))) result = DbType.Decimal; else if( type.Equals(typeof(DateTime))) result = DbType.DateTime; else if( type.Equals(typeof(bool))) result = DbType.Boolean; else if( type.Equals(typeof(string) ) ) result = DbType.String; else if( type.Equals(typeof(decimal))) result = DbType.Decimal; else if( type.Equals(typeof(byte[]))) result = DbType.Binary; else if( type.Equals(typeof(Guid))) result = DbType.Guid; return result; } #endregion UpdateTable#region UpdateTable public static void UpdateTable(DataTable dt,string TableName,string KeyName) { foreach(DataRow dr in dt.Rows) { updateRow(dr,TableName,KeyName); } } #endregion InsertTable#region InsertTable //用于主键是数据库表名+ID类型的 public static void InsertTable(DataTable dt) { string TableName="["+dt.TableName+"]"; string KeyName=dt.TableName+"ID"; foreach(DataRow dr in dt.Rows) { insertRow(dr,TableName,KeyName); } } //用于主键是任意类型的 public static void InsertTable(DataTable dt,string KeyName) { string TableName="["+dt.TableName+"]"; foreach(DataRow dr in dt.Rows) { insertRow(dr,TableName,KeyName); } } #endregion DeleteTable#region DeleteTable public static void DeleteTable(DataTable dt,string KeyName) { string TableName="["+dt.TableName+"]"; foreach(DataRow dr in dt.Rows) { deleteRow(dr,TableName,KeyName); } } #endregion updateRow#region updateRow private static void updateRow(DataRow dr,string TableName,string KeyName) { if (dr[KeyName]==DBNull.Value ) { throw new Exception(KeyName +"的值不能为空"); } if (dr.RowState ==DataRowState.Deleted) { deleteRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Modified ) { midifyRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Added ) { insertRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Unchanged ) { midifyRow(dr,TableName,KeyName); } } #endregion deleteRow#region deleteRow private static void deleteRow(DataRow dr,string TableName,string KeyName) { string sql="Delete {0} where {1} =@{1}"; DataTable dtb=dr.Table ; sql=string.Format(sql,TableName,KeyName); SqlCommand sqlcom=new SqlCommand(sql); System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ KeyName; iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType); iparam.Value = dr[KeyName]; sqlcom.Parameters .Add(iparam); ExecCommand(sqlcom); } #endregion midifyRow#region midifyRow private static void midifyRow(DataRow dr,string TableName,string KeyName) { string UpdateSql = "Update {0} set {1} {2}"; string setSql="{0}= @{0}"; string wherSql=" Where {0}=@{0}"; StringBuilder setSb = new StringBuilder(); SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table; for (int k=0; k<dr.Table.Columns.Count; ++k) { System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters .Add(iparam); if (dtb.Columns[k].ColumnName==KeyName) { wherSql=string.Format(wherSql,KeyName); } else { setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName)); setSb.Append(","); } } string setStr=setSb.ToString(); setStr=setStr.Substring(0,setStr.Length -1); //trim , string sql = string.Format(UpdateSql, TableName, setStr,wherSql); sqlcom.CommandText =sql; try { ExecCommand(sqlcom); } catch(Exception ex) { throw ex; } } #endregion insertRow#region insertRow private static void insertRow(DataRow dr,string TableName,string KeyName) { string InsertSql = "Insert into {0}({1}) values({2})"; SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table ; StringBuilder insertValues = new StringBuilder(); StringBuilder cloumn_list = new StringBuilder(); for (int k=0; k<dr.Table.Columns.Count; ++k) { //just for genentae， if (dtb.Columns[k].ColumnName==KeyName) continue; System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters .Add(iparam); cloumn_list.Append(dtb.Columns[k].ColumnName); insertValues.Append("@"+dtb.Columns[k].ColumnName); cloumn_list.Append(","); insertValues.Append(","); } string cols=cloumn_list.ToString(); cols=cols.Substring(0,cols.Length -1); string values=insertValues.ToString(); values=values.Substring(0,values.Length -1); string sql = string.Format(InsertSql, TableName,cols ,values); sqlcom.CommandText =sql; try { ExecCommand(sqlcom); } catch(Exception ex) { throw ex; } } #endregion }} Insert #region Insert private void InsertUserInfo() { DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserInfoID"; DataRow dr=dt.NewRow(); dr["LoginName"]=this.txtUserName.Value; dr["Pass"]=this.txtPassword.Value; dr["NickName"]=this.txtNickName.Value; dr["UserType"]=1; dr["IsActive"]=false; dr["RegisterDate"]=System.DateTime.Now; dt.Rows.Add(dr); dt.AcceptChanges(); DataHelper.InsertTable(dt,keyname); } #endregion Update #region Update private void UpdateUserInfo(string UserID) { DataSet ds=GetUserOther(UserID); DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserID"; DataRow dr=dt.Rows[0]; dr["LoginName"]=this.txtUserName.Value; dr["Pass"]=this.txtPassword.Value; dr["NickName"]=this.txtNickName.Value; dr["UserType"]=1; dr["IsActive"]=false; dr["RegisterDate"]=System.DateTime.Now; dt.Rows.Add(dr); dt.AcceptChanges(); DataHelper.UpdateTable(dt,dt.TableName,keynanme); } #endregion Delete #region Delete private void DeleteUserInfo(string UserID) { DataSet ds=GetUserOther(UserID); DataTable dt=ds.Tables[0]; dt.TableName="UserInfo"; string keyname="UserID"; DataHelper.DeleteTable(dt,keyname); } #endregion 转载地址：https://linuxstyle.blog.csdn.net/article/details/1534271 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
哈哈，博客排版真的漂亮呢~
[***.90.31.176]2024年04月12日 17时51分12秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
python编码
2019-04-30
scala maven plugin
2019-04-30
flink 1-个人理解
2019-04-30
redis cli
2019-04-30
redis api
2019-04-30
flink physical partition
2019-04-30
java 解析json
2019-04-30
java http请求
2019-04-30
tensorflow 数据格式
2019-04-30
tf rnn layer
2019-04-30
常用中间件
2019-04-30
tf input layer
2019-04-30
tf model create
2019-04-30
tf dense layer两种创建方式的对比和numpy实现
2019-04-30
tf initializer
2019-04-30
tf 从RNN到BERT
2019-04-30
tf keras SimpleRNN源码解析
2019-04-30
tf keras Dense源码解析
2019-04-30
tf rnn输入输出的维度和权重的维度
2019-04-30
检验是否服从同一分布
2019-04-30
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 310817217 位访客
访问时间: 2024-05-04 21:12:48
访问IP: 18.118.171.20
Copyright © 2020 - 2023 blog.css8.cn 京ICP备2021015314号-1
手机版