[ASP.NET2.0]Common层通用代码之数据库访问

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Common
...{
    public class DBCom
    ...{
        //取得一个数据库的连接
        public static SqlConnection GetConnection()
        ...{
            SqlConnection sqlconn = new SqlConnection();
            try
            ...{
                sqlconn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
                return sqlconn;
                
            }
            catch(SqlException e)
            ...{
                System.Console.WriteLine( e.Message.ToString());
                return null;
            }
        }

        //========================================================================

        //执行sql语句返回所影响的行数
        public static int ExecuteSql(string sql)
        ...{
            SqlConnection conn = DBCom.GetConnection();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql.Trim();
            try
            ...{
                conn.Open();
                int count = cmd.ExecuteNonQuery();
                return count;
            }
            catch(SqlException ex)
            ...{
                System.Console.WriteLine(ex.Message.ToString());
                return 0;
            }
        }

        //执行存储过程
        public static int ExecuteProc(string procName, SqlParameter[] sp)
        ...{
            SqlConnection conn = DBCom.GetConnection();
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = procName.Trim();
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter s in sp)
            ...{
                cmd.Parameters.Add(s);
            }

            try
            ...{
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
            catch (System.Exception ex)
            ...{
                System.Console.WriteLine(ex.Message.ToString());
                return 0;
            }
        }

        //==============================================================================

        //根据sql 返回数据
        public static SqlDataReader GetDataReader(string sql)
        ...{
            SqlConnection conn = DBCom.GetConnection();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql.Trim();
            try
            ...{
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                return dr;
            }
            catch (SqlException ex)
            ...{
                System.Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        //===========================================================================

        public static DataSet GetDataSet(string Sql, string TableName)
        ...{
            SqlConnection conn = DBCom.GetConnection();
            try
            ...{
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(Sql, conn);
                DataSet ds = new DataSet();
                adapter.Fill(ds, TableName);
                conn.Close();
                return ds;
            }
            catch (SqlException ex)
            ...{
                System.Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }
       
        //通过存储过程返回 数据集
        public static DataSet GetDataSet(string procname, SqlParameter[] sp, string tableName)
        ...{
            SqlConnection conn = DBCom.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procname.Trim();

            try
            ...{
                conn.Open();
                cmd.Connection = conn;
                foreach (SqlParameter p in sp)
                ...{
                    if (p == null)
                    ...{
                        break;
                    }
                    else
                    ...{
                        cmd.Parameters.Add(p);
                    }
                    
                }

                cmd.ExecuteNonQuery();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds, tableName);
                conn.Close();
                return ds;
            }
            catch(SqlException ex)
            ...{
                System.Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

    }
}