告别ADO.NET实现应用系统无缝切换的烦恼(4)_ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public  bool Exist(string Sql)
        {
            bool exist;
            this.Open();
            cmd.CommandText = Replace(Sql);
            DbDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                exist = true;   //记录存在
            }
            else
            {
                exist = false;  //记录不存在
            }
            dr.Close();
            this.Close();
            return exist;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        public  void ExecSql(string Sql)
        {
            try
            {
                this.Open();
                cmd.CommandText = Replace(Sql);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }


        }

        /// <summary>
        /// 执行SQL语句,返回一个单值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ReturnValue(string Sql)
        {
            object returnValue = string.Empty;
            try
            {
                this.Open();
                cmd.CommandText = Replace(Sql);
                returnValue = cmd.ExecuteScalar();
                if (returnValue == null)
                {
                    returnValue = string.Empty;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return returnValue.ToString();
        }

        /// <summary>
        /// 执行多条SQL语句并启用数据库事务
        /// </summary>
        /// <param name="SQLStringList"></param>
        public  bool ExecSqlTran(List<String> SQLStringList)
        {
                this.Open();
                DbTransaction trans = conn.BeginTransaction();
                cmd.Transaction = trans;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        cmd.CommandText = Replace(SQLStringList[n]);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    this.Close();
                }

        }

        /// <summary>
        /// 执行存储过程并返回结果集
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedure(string storedProcName)
        {
            DataSet ds = new DataSet();
            try
            {
                this.Open();
                cmd.CommandText = storedProcName;
                cmd.CommandType = CommandType.StoredProcedure;
                Adapter.SelectCommand = cmd;
                //Adapter.SelectCommand.CommandTimeout = 1200;//可以设置适当的超时时间(秒)，避免选择时间段过大导致填充数据集超时
                Adapter.Fill(ds);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();

            }
            return ds;

        }

        /// <summary>
        /// 执行存储过程，方法不返回结果集
        /// </summary>
        /// <param name="storedProcName"></param>
        public void RunVoidProcedure(string storedProcName)
        {
            cmd.CommandText = storedProcName;
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Open();
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 将实体类的属性进行参数转换(ORACLE测试通不过，必须要求所有参数都包含在语句中才行)
        /// </summary>
        /// <param name="model"></param>
        /// <param name="ParaCollect"></param>
        //public void ConvertToParameters(object model, DbParameterCollection ParaCollect)
        //{
        //    Type T = model.GetType();
        //    PropertyInfo[] propert = T.GetProperties();

        //    for (int i = 0; i < propert.Length; i++)
        //    {
        //        AddParam(propert[i].Name, propert[i].GetValue(model, null), ParaCollect);
        //    }
        //}

        /// <summary>
        /// 将实体类的属性进行参数转换
        /// </summary>
        /// <param name="model"></param>
        /// <param name="ParaCollect"></param>
        public void ConvertToParameters(object model, DbParameterCollection ParaCollect,List<string> fields)
        {
            Type T = model.GetType();
            PropertyInfo[] propert = T.GetProperties();

            for (int i = 0; i < propert.Length; i++)
            {
                if (fields.Contains(propert[i].Name)) //检测必须参数化的实体属性
                {
                    AddParam(propert[i].Name, propert[i].GetValue(model, null), ParaCollect);
                }
            }
        }

        /// <summary>
        /// 通过反射将取出的数据写入实体类(ORACLE测试通不过,需进行类型强制转换)
        /// </summary>
        /// <param name="model"></param>
        /// <param name="cmdText"></param>
        //public void GetModel(object model, string cmdText)
        //{
        //    PropertyInfo propertyInfo;
        //    DbDataReader dr = ExecuteDataReader(conn, cmdText);
        //    while (dr.Read())
        //    {
        //        for (int i = 0; i < dr.FieldCount; i++)
        //        {
        //            propertyInfo = model.GetType().GetProperty(dr.GetName(i));
        //            if (propertyInfo != null)
        //            {
        //                if (dr.GetValue(i) != DBNull.Value)
        //                {
        //                    //Type t = dr.GetValue(i).GetType();
        //                    propertyInfo.SetValue(model, dr.GetValue(i), null);
        //                }
        //            }
        //        }
        //    }
        //    dr.Close();
        //    conn.Close();
        //}

        /// <summary>
        /// 通过反射将数据绑定到实体对象，由于不同数据库对应于.NET的数据类型不一样
        /// 需做强制类型转换
        /// </summary>
        /// <param name="model"></param>
        /// <param name="cmdText"></param>
        public void GetModel(object model, string cmdText)
        {
            PropertyInfo propertyInfo;
            DbDataReader dr = ExecuteDataReader(conn, cmdText);
            object _value;
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    propertyInfo = model.GetType().GetProperty(dr.GetName(i));
                    if (propertyInfo != null && dr.GetValue(i) != DBNull.Value)
                    {
                        switch (propertyInfo.PropertyType.ToString())
                        {
                            case "System.String":
                                {
                                    _value = Convert.ToString(dr.GetValue(i));//字符串是全球通用类型，也可以不用转换
                                    propertyInfo.SetValue(model, _value, null);
                                }break;

                            case "System.Int32":
                                {
                                    _value = Convert.ToInt32(dr.GetValue(i));
                                    propertyInfo.SetValue(model, _value, null);
                                } break;

                            case "System.Single":
                                {
                                    _value = Convert.ToSingle(dr.GetValue(i));
                                    propertyInfo.SetValue(model, _value, null);
                                } break;

                            case "System.Decimal":
                                {
                                    _value = Convert.ToDecimal(dr.GetValue(i));
                                    propertyInfo.SetValue(model, _value, null);
                                } break;

                            case "System.Double":
                                {
                                    _value = Convert.ToDouble(dr.GetValue(i));
                                    propertyInfo.SetValue(model, _value, null);
                                } break;

                            case "":
                                {
                                    _value = Convert.ToDateTime(dr.GetValue(i));
                                    propertyInfo.SetValue(model, _value, null);
                                } break;

                            default: break;
                        }

                    }
                }
            }
            dr.Close();
            conn.Close();
        }

        /// <summary>
        /// 根据不同的数据库命令对象返回该类型数据库参数的前缀格式化字符串
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private string GetParameterFormat(DbCommand command)
        {

            if (!ParametersFormat.ContainsKey(command.GetType()))
            {
                this.Open();//读取参数前缀时需打开数据库连接
                ParametersFormat.Add(
                   command.GetType(),
                   command.Connection.GetSchema("DataSourceInformation")
                      .Rows[0]["ParameterMarkerFormat"].ToString());
                //conn.Close();在真正执行语句的时候去关闭，避免重复打开
            }
            return ParametersFormat[command.GetType()];
        }

        private void Open()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        private void Close()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 替换DML语句里的参数前缀
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public string Replace(string str)
        {
            return str.Replace("$", retParaformat.Substring(0, 1));
        }
    }
}

PeaceHelper.rar

(责任编辑：admin)

ADO.NET中的表映射	C#引用System.Data.SQLite操作SQLite数据库一例
打开和关闭数据库连接的3种方式	浅谈ExecuteNonQuery,ExecuteReader和ExecuteSca
ADO.NET数据访问方式：SqlDataReader	OleDbDataReader的一些属性和方法
ActiveRecord多表查询，返回DataTable	DataSet,DataTable,DataView
DataTable数据导出到EXCEL文件	告别ADO.NET实现应用系统无缝切换的烦恼
DataTable转换成JSON字符串的函数	.NET中统一的存储过程调用方法
SQL语句之普通行列转换	DataTable.Compute强大的功能
将DataRow转换成相应的对象（通用以及泛型操作）	ADO.NET与ADO在数据内存中的差异
XamDataGrid控件显示checkbox并绑定DataTable	DataSet DataTable获得总记录数

ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

热门标签

告别ADO.NET实现应用系统无缝切换的烦恼(4)

Tags：ADO.NET