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;
}
}