简单数据库操作，连接数据库，查询数据

 

1、建一个sql实例，打开一次长连接，多次操作。

     优点：连接比较耗时，一次打开多次操作，适合频繁操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace DAL
{
    public class Dbhelper
    {
        private string connectstring = string.Empty;
        public Dbhelper(string connectstring)
        {
            this.connectstring = connectstring;
        }

        public int ExecSql(string sql)
        {
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectstring))
            {
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }
            
        }

        public int ExecuteNonQuery(string sql)
        {
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectstring))
            {
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }
        }

        public System.Data.DataSet ExcuteGetDateSet(string sql)
        {
            return ExcuteGetDateSet(sql, null);
        }

        public System.Data.DataSet ExcuteGetDateSet(string sql, System.Data.OleDb.OleDbParameter[] parameters)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectstring))
            {
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                cmd.CommandText = sql;
                if (parameters != null)
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    foreach (var item in parameters)
                    {
                        cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
                        cmd.Parameters[cmd.Parameters.Count - 1].DbType = item.DbType;
                        cmd.Parameters[cmd.Parameters.Count - 1].Direction = item.Direction;
                        cmd.Parameters[cmd.Parameters.Count - 1].Size = item.Size;
                    }
                }
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds;
        }


    }
}

 

2、每次操作时打开，用完关闭。

    优点：每次操作都是一个单独的线程，一个连接占用一份server资源，在数据量大的情况下，多几个连接会提高性能。相当于多线程。

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    public class Dbhelper2
    {
        private string connectstring = string.Empty;
        private System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();

        public Dbhelper2(string connectstring)
        {
            this.connectstring = connectstring;
        }

        public bool Connect()
        {
            try
            {
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.ConnectionString = connectstring;
                    conn.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;

        }

        public void Close()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public int ExecSql(string sql)
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }
            return -1;
        }

        public System.Data.DataSet ExcuteGetDateSet(string sql)
        {
            return ExcuteGetDateSet(sql, null);
        }

        public System.Data.DataSet ExcuteGetDateSet(string sql, System.Data.OleDb.OleDbParameter[] parameters)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            if (conn.State == System.Data.ConnectionState.Open)
            {
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                cmd.CommandText = sql;
                if (parameters != null)
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    foreach (var item in parameters)
                    {
                        cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
                        cmd.Parameters[cmd.Parameters.Count - 1].DbType = item.DbType;
                        cmd.Parameters[cmd.Parameters.Count - 1].Direction = item.Direction;
                        cmd.Parameters[cmd.Parameters.Count - 1].Size = item.Size;
                    }
                }
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds;
        }
    }

}

　　

3、调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Host
{
    namespace Host
    {
        class Program
        {
            static void Main(string[] args)
            {
                //以下三种连接字符串都正确，各个属性加单引号 不加单引号都可以，但是local一定要加括号
                //string connectstring = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=test;User id=sa;Password=FAS321";
                //string connectstring = "Provider='SQLOLEDB';Data Source=(local);Initial Catalog='test';User id='sa';Password='FAS321'";
                string connectstring = "Provider='SQLOLEDB';Data Source=.;Initial Catalog='test';User id='sa';Password='FAS321'";

                DAL.Dbhelper helper = new DAL.Dbhelper(connectstring);
                string sql = "select * from m_Dept";
                DataSet ds = helper.ExcuteGetDateSet(sql);

                DAL.Dbhelper2 helper2 = new DAL.Dbhelper2(connectstring);
                helper2.Connect();
                string sql2 = "select * from m_Dept";
                DataSet ds2 = helper2.ExcuteGetDateSet(sql2);
                helper2.Close();
            }
        }
    }
}

　　

。