ado.net 不建立实体映射，直接通过sqlserver sql 语句查询数据库结果

 

ado.net 不建立实体映射，直接通过sqlserver sql 语句查询数据库结果

注意细节：

1、有的字段是枚举类型的，需要引用自己枚举类型，再将字段进行转换。

或者有的字段是其它特殊类型的，也需要引用相应的类型对象，进行字段的类型转换。否则系统不知道你要转成什么类型的字段，会报错，显示转换失败。

2、因为该方法是公共的，也就是整个系统的所有调用该方法进行查询的，都会用到，所以，这里会存在各个模块对象的字段转换类型。不建立各自的实体映射，就是会存在这种情况。

 

 

        /// <summary>
        /// 生产库sql查询；V20220308版本；
        /// </summary>
        /// <typeparam name="TElement"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pareameters"></param>
        /// <returns></returns>
        public List<T> ProductSqlQuery<TElement>(string sql)
        {
            string methodName = "RepositoryBase.cs.public List<T> ProductSqlQuery<TElement>(string sql);";
            //生产库
            //string connectionString = @"Server=192.168.10.233;initial catalog=CloudBSS_2021-01-15_Produce;UID=ylcrm;PWD=123456789;Application Name=2021-01-15_Produce;Max Pool Size = 100;MultipleActiveResultSets=true;App=EntityFramework;";

            //测试库
            //string connectionString = @"Server=192.168.10.230;initial catalog=CloudBSS_Dev_20220211;UID=ylcrm;PWD=123456789;Application Name=Dev_20220211;Max Pool Size = 100;MultipleActiveResultSets=true;";

            //生产库20220426
            string connectionString = @"Server=192.168.10.233;initial catalog=Production_20220426;UID=ylcrm;PWD=123456789;Application Name=Production_20220426_Application;Max Pool Size = 100;MultipleActiveResultSets=true;App=EntityFramework;";

            string logStr = "";

            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, sqlConn);
                try
                {
                    sqlConn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                   List<T> entityList = new List<T>();
                    Type entityType = typeof(T);
                    using (dr)
                    {
                        //读取每一行
                        while (dr.Read())
                        {
                            T entity = new T();
                            //遍历当前行的每一列
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                object currentRow_ZiDuanZhi = dr[i];
                                if (currentRow_ZiDuanZhi != null)
                                {
                                    logStr = "";//每次循环清空
                                    logStr += ("字段值：" + currentRow_ZiDuanZhi + "；");
                                    string currentRow_ZiDuanMing = dr.GetName(i);
                                    PropertyInfo ziDuanMing_PropertyInfo = entityType.GetProperty(currentRow_ZiDuanMing);
                                    logStr += ("字段名：" + currentRow_ZiDuanMing + "；");

                                    if ("ContractId".ToLower().Equals(currentRow_ZiDuanMing.ToLower()))
                                    {
                                    }
                                    if ("State".ToLower().Equals(currentRow_ZiDuanMing.ToLower()))
                                    {
                                    }

                                    Type ziDuanMing_Type = ziDuanMing_PropertyInfo.PropertyType;
                                    object convertResultObject = null;

                                    if (ziDuanMing_Type.Equals(typeof(DateTime?)))
                                    {
                                        if (!string.IsNullOrWhiteSpace(currentRow_ZiDuanZhi.ToString()))
                                        {
                                            convertResultObject = (DateTime?)currentRow_ZiDuanZhi;
                                        }
                                    }
                                    else if (ziDuanMing_Type.Equals(typeof(ProductTypeEnum)))
                                    {
                                        convertResultObject = (ProductTypeEnum)currentRow_ZiDuanZhi;
                                    }
                                    else if (ziDuanMing_Type.Equals(typeof(Common.Enums.SysEnum.SalesStatus)))
                                    {
                                        convertResultObject = (Common.Enums.SysEnum.SalesStatus)currentRow_ZiDuanZhi;
                                    }
                                    else if (ziDuanMing_Type.Equals(typeof(EBusinessState)))
                                    {
                                        convertResultObject = (EBusinessState)currentRow_ZiDuanZhi;
                                    }
                                    else if (ziDuanMing_Type.Equals(typeof(int?)))
                                    {
                                        string currentRow_ZiDuanZhi_Str = currentRow_ZiDuanZhi.ToString();
                                        if (!string.IsNullOrWhiteSpace(currentRow_ZiDuanZhi_Str))
                                        {
                                            convertResultObject = (int?)(currentRow_ZiDuanZhi);
                                        }
                                        else
                                        {
                                            convertResultObject = (int?)null;
                                        }
                                    }
                                    else if (ziDuanMing_Type.Equals(typeof(decimal?)))
                                    {
                                        if (!string.IsNullOrWhiteSpace(currentRow_ZiDuanZhi.ToString()))
                                        {
                                            convertResultObject = (decimal?)currentRow_ZiDuanZhi;
                                        }
                                    }
                                    else
                                    {
                                        convertResultObject = Convert.ChangeType(currentRow_ZiDuanZhi, ziDuanMing_Type);
                                    }
                                    ziDuanMing_PropertyInfo.SetValue(entity, convertResultObject, null);
                                }
                            }
                            entityList.Add(entity);
                        }
                    }
                    return entityList;
                 }
                catch (Exception ex)
                {
                    string logResult = logStr;
                    cmd.Dispose();
                    sqlConn.Close();

                    LogHelper.Error(methodName + ex + logStr);
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    sqlConn.Close();
                }
           }
        }

 

 

 

搜索

复制