三层中最重要的SqlHelper类

1：连接数据库（在表示层中添加应用配置文件，写上以下代码）

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="constr" connectionString="server=.;uid=sa;pwd= ;database=DB" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

2: 定义静态只读的连接字符，并在构造中给赋值

连接字符串STRCONN

private static readonly string  STRCONN= null;

        static SqlHelper()
        {
            STRCONN= System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        }

3: 返回受影响的行数（通常我们在创建存储过程的时候，为了提升性能，都会使用set nocount on 的代码关掉受影响的行数，但是我们可以通过返回@@error的数量是否为零，从而判断，调用该方法执行是否成功！）

方法ExecuteNonQuery()

        public static int ExecuteNonQuery(string cmdText, CommandType commandType = CommandType.StoredProcedure, params SqlParameter[] values)
        {
            int result = -1;
            using (SqlConnection conn=new SqlConnection(STRCONN))
            {
                using (SqlCommand cmd=new SqlCommand(cmdText,conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(values);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    if (cmd.Parameters.Contains("@ReturnValue"))
                    {
                        object error = cmd.Parameters["@ReturnValue"].Value;
                        if (error != null && error != DBNull.Value)
                        {
                            result = Convert.ToInt32(error);
                        }
                    }
                }
            }
            return result;
        }

4: 用于实体中的属性和数据库中的字段相关联对应（赋值）

方法ExecuteDataReader()

private static T ExecuteDataReader<T>(SqlDataReader dr)
        {
            T obj = default(T);
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            int columnCount = dr.FieldCount;
            obj = Activator.CreateInstance<T>();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                string propertyName = propertyInfo.Name;
                for (int i = 0; i < columnCount; i++)
                {
                    string columnName = dr.GetName(i);
                    if (string.Compare(propertyName, columnName, true) == 0)
                    {
                        object value = dr.GetValue(i);
                        if (value != null && value != DBNull.Value)
                        {
                            propertyInfo.SetValue(obj, value, null);
                        }
                        break;
                    }
                }
            }
            return obj;
        }

5: 返回一个对象

方法ExecuteEntity()

public static T ExecuteEntity<T>(string cmdText, CommandType commandType = CommandType.StoredProcedure, params SqlParameter[] values)
        {
            T obj = default(T);
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(values);
                    con.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            obj = ExecuteDataReader<T>(dr);
                            break;
                        }
                    }
                }
            }
            return obj;
        }

6: 返回List<T>集合

方法ExecuteList()

public static List<T> ExecuteList<T>(string cmdText,CommandType commandType= CommandType.StoredProcedure,params SqlParameter[] values)
        {
            List<T> list = new List<T>();
            using (SqlConnection conn=new SqlConnection(STRCONN))
            {
                using (SqlCommand cmd=new SqlCommand(cmdText,conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(values);
                    conn.Open();
                    using (SqlDataReader dr=cmd.ExecuteReader( CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            T obj = ExecuteDataReader<T>(dr);
                            list.Add(obj);
                        }
                    }
                }
            }
            return list;
        }

 

转载于:https://www.cnblogs.com/jeffqing/archive/2012/10/31/2748690.html