dbservice.cs

配合实体类的数据库访问层

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


namespace LWDAL
{
    public class DBService
    {
        string conStr =VarInWhole.constr_static;
        SqlConnection connection;
        SqlCommand command;
        SqlDataAdapter dataAdapter;
        SqlDataReader dataReader;
        public bool RunSql(string sql)
        {
            try
            {
                connection = new SqlConnection(conStr);
                connection.Open();
                command = new SqlCommand(sql, connection);
                int count = command.ExecuteNonQuery();
                connection.Close();
                if (count > 0)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            {                
                return false;
            }
        }
        public bool RunSql(string sql,SqlParameter[] parameters)
        {
            try
            {
                connection = new SqlConnection(conStr);
                connection.Open();
                command = new SqlCommand();
                command.CommandText = sql;
                command.Connection = connection;
                command.Parameters.AddRange(parameters);
                int count = command.ExecuteNonQuery();
                connection.Close();
                if (count > 0)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
        public bool Runproc(string sql, SqlParameter[] parameters)
        {
            try
            {
                connection = new SqlConnection(conStr);
                connection.Open();
                command = new SqlCommand();
                command.CommandText = sql;
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = connection;
                command.Parameters.AddRange(parameters);
                int count = command.ExecuteNonQuery();
                connection.Close();
                if (count > 0)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
        public DataTable Select(string sql, string tableName)
        {
            connection = new SqlConnection(conStr);
            dataAdapter = new SqlDataAdapter(sql, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, tableName);
            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                Console.WriteLine("{0},{1}", row[0], row[1]);
            }
            return dataSet.Tables[tableName];
        }
        public List<T> Select<T>(string sql,SqlParameter[] parameters) 
        {
            Type type = typeof(T);
            connection = new SqlConnection(conStr);
            connection.Open();
            command = new SqlCommand();
            command.CommandTimeout = 180;
            command.CommandText = sql;
            command.Connection = connection;
            command.Parameters.AddRange(parameters);            
            dataReader=command.ExecuteReader();
            List<T> list = new List<T>();
            string[] fields = new string[dataReader.FieldCount];
            for (int i = 0; i < dataReader.FieldCount ; i++) 
            {
                fields[i] = dataReader.GetName(i);
            }
            while (dataReader.Read())
            {
                PropertyInfo[] pros = type.GetProperties();
                Object obj = Activator.CreateInstance(type);
                foreach(string str in fields)
                {
                    foreach (PropertyInfo property in pros)
                    {
                        if (str.ToLower() == property.Name.ToLower())
                        {                                                        
                            if (!Convert.IsDBNull(dataReader[str]))
                            {
                                property.SetValue(obj, dataReader[str], null);
                            }                                                   
                            Console.WriteLine(property.GetValue(obj,null));
                            break;
                        }
                    }
                }                    
                list.Add(((T)obj));
            }            
            dataReader.Close();
            connection.Close();
            if(list.Count==0)
            {
                return null;
            }
            return list;
        }
        public List<T> Select<T>(string sql)
        {
            Type type = typeof(T);
            connection = new SqlConnection(conStr);
            connection.Open();
            command = new SqlCommand(sql, connection);            
            dataReader=command.ExecuteReader();
            List<T> list = new List<T>();
            string[] fields = new string[dataReader.FieldCount];
            for (int i = 0; i < dataReader.FieldCount ; i++) 
            {
                fields[i] = dataReader.GetName(i);
            }
            while (dataReader.Read())
            {
                PropertyInfo[] pros = type.GetProperties();
                Object obj = Activator.CreateInstance(type);
                foreach(string str in fields)
                {
                    foreach (PropertyInfo property in pros)
                    {
                        if (str.ToLower() == property.Name.ToLower())
                        {
                            if (!Convert.IsDBNull(dataReader[str]))
                            {
                                property.SetValue(obj, dataReader[str], null);
                            }  
                            Console.WriteLine(property.GetValue(obj,null));
                            break;
                        }
                    }
                }                    
                list.Add(((T)obj));
            }            
            dataReader.Close();
            connection.Close();
            if(list.Count==0)
            {
                return null;
            }
            return list;
        }
    }
}