C# 连接DB的几种方法

C# 连接SQLCE

1.命名空间

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.Web.Configuration;

public class MySQLHelper
    {
        private static SqlCeConnection con;
        private static SqlCeCommand cmd;
        private static SqlCeTransaction Trans;
        /// <summary>
        /// 创建连接DB
        /// </summary>
        /// <returns></returns>
        private  SqlCeConnection CreateConnection()
        {
            string strcon = null;
            //constr 为在Web.config中写连接DB字符串
            // <connectionStrings>
            //  <add name="constr" connectionString="Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=TestDB;User ID=sa;Password=123;" />
            //</connectionStrings>
            strcon = WebConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            return new SqlCeConnection(strcon);
        }

        /// <summary>
        /// 返回Datatable
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        private  DataTable GetDataTable(string strSQL )
        {
            DataTable dt = new DataTable();
            SqlCeDataAdapter da;
            try
            {
                con = CreateConnection();
                con.Open();
                try
                {
                    SqlCeCommand dm0 = new SqlCeCommand(strSQL,con);
                    da = new SqlCeDataAdapter(dm0);
                    da.Fill(dt);
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="strsql"></param>
        /// <returns></returns>
        private  DataSet GetDataSet(string strsql)
        {
          DataSet ds = new DataSet();
          SqlCeDataAdapter da;
          try
          {
              con = CreateConnection();
              con.Open();
              try
              {
                  SqlCeCommand cmd0 = new SqlCeCommand(strsql,con);
                  da = new SqlCeDataAdapter(cmd0);
                  da.Fill(ds,"table1");
              }
              catch(Exception ex)
              {
                  throw ex;
              }
          }
          catch(Exception ex)
          {
              throw ex;
          }
          return ds;
        }

        /// <summary>
        /// 执行删除或者修改
        /// </summary>
        /// <param name="strsql"></param>
        private void  ModifyData(string strsql)
        {
            string Message="";
            try
            {
                con = CreateConnection();
                con.Open();
                cmd = new SqlCeCommand();
                cmd.Connection = con;
                Trans = con.BeginTransaction();
                cmd.Transaction = Trans;
                 try
                {
                    cmd.CommandText = strsql;
                    cmd.ExecuteNonQuery();
                 }
                 catch (Exception ex)
                 {
                     Message = ex.Message;
                 }
                 if (string.IsNullOrEmpty(Message))
                 {
                     Trans.Commit();
                 }
                 else
                 {
                     Trans.Rollback();
                 }
                 con.Close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }

2.连接Sql server

 public class SQLHelper
    {
        private OleDbConnection con;
        private OleDbCommand Cmd;
        private OleDbTransaction Trans;

        //创建连接
        public static OleDbConnection CreateConn()
        {
            string constr = null;
            constr = "Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=TestDB;User ID=sa;Password=123;";
           // constr = WebConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            return new OleDbConnection(constr);
        }

        //进行资料库的查询
        public DataSet GetDataSet(string strsql)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter da;
            try
            {
                con = CreateConn();
                con.Open();
                try
                {
                    OleDbCommand cmd1 = new OleDbCommand(strsql, con);
                    da = new OleDbDataAdapter(cmd1);
                    da.Fill(ds, "table1");
                }
                catch (Exception ex)
                {
                    throw ex;
                }
       
            }
            catch (Exception ex)
            {
                throw ex;
            }
           finally
           {
               if (con.State == ConnectionState.Open)
               {
                   con.Close();
               }
           }
           return ds;
        }

        //进行资料库的修改
        public bool  Modifydata(string strsql)
        {
            bool blMsg = false;

            try
            {
                con = CreateConn();
                con.Open();

                Cmd = new OleDbCommand();
                Cmd.Connection = con;

                Trans = con.BeginTransaction();
                Cmd.Transaction = Trans;

                try
                {
                    Cmd.CommandText = strsql;
                    Cmd.ExecuteNonQuery();
                    blMsg = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                if (blMsg == true)
                {
                    Trans.Commit();
                }
                else
                {
                    Trans.Rollback();
                }
                con.Close();
            }
            catch (Exception ex)
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
              throw ex;
            }
            return blMsg;
        }
        public string Modifydata1(string strsql)
        {
            string ttMsg = "";

            try
            {
                con = CreateConn();
                con.Open();

                Cmd = new OleDbCommand();
                Cmd.Connection = con;

                try
                {
                    Cmd.CommandText = strsql;
                    Cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    ttMsg = ex.Message;
                }
                con.Close();
            }
            catch (Exception ex)
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                ttMsg = ex.Message;
            }
            return ttMsg;
        }
        public delegate T ConvertToEntityDelegate<T>(IDataReader reader);
        //使用泛型
        public List<T> GetList<T>(string strsql, ConvertToEntityDelegate<T> GetEntityFunc)
        {
            List<T> listResult = new List<T>();
            try
            {
                con = CreateConn();
                con.Open();

                Cmd = new OleDbCommand();
                Cmd.Connection = con;

                Trans = con.BeginTransaction();
                Cmd.Transaction = Trans;
                IDataReader reader = null;
                try
                {
                    Cmd.CommandText = strsql;
                    reader = Cmd.ExecuteReader();
                    while (!reader.IsClosed && reader.Read())
                    {
                        T entity = GetEntityFunc(reader);
                        if (entity != null)
                            listResult.Add(entity);
                    } 
                }
                catch
                {
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return listResult;
        }
        //进行资料库的修改
        public int  Deletedata(string strsql)
        {
            int icout = 0;

            try
            {
                con = CreateConn();
                con.Open();

                Cmd = new OleDbCommand();
                Cmd.Connection = con;

                Trans = con.BeginTransaction();
                Cmd.Transaction = Trans;

                try
                {
                    Cmd.CommandText = strsql;
                 icout =  Cmd.ExecuteNonQuery();
                  
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                if (icout > 0)
                {
                    Trans.Commit();
                }
                else
                {
                    Trans.Rollback();
                }
                con.Close();
            }
            catch (Exception ex)
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                throw ex;
            }
            return icout;
        }

    }