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();
}
}
}
}
。