程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SyBase數據庫 >> SyBase教程 >> 使用oledb對數據庫進行增刪改查及批量插入操作

使用oledb對數據庫進行增刪改查及批量插入操作

編輯:SyBase教程

使用oledb對數據庫進行增刪改查及批量插入操作


使用oledb操作數據庫工具類,可使用泛型統一操作

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;


namespace CommonUtil
{
public class DataBaseUtil
{

//傳遞數據庫文件路徑,這裡使用的是access2007數據庫
public DataBaseUtil(string path)
{
Path = path;
ConnStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False; ",
path);
conn = new OleDbConnection(ConnStr);
}
public string Path;
public static string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\numdb.accdb;Persist Security Info=False; ";

private OleDbConnection conn= new OleDbConnection(ConnStr);//創建一個connection對象

//使用泛型,獲取所有的實體類對象,返回list
public List ReieveList() where T : class,new()
{
try
{
var className = (typeof(T)).Name;
var sql = string.Format("SELECT * FROM {0}", className);
OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr);
DataSet ds = new DataSet();
da.Fill(ds);
var dt = ds.Tables[0];

var list = ConverterUtil.ConvertDataTableToList(dt);

return list;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return null;
}

}

//同上,根據條件,查詢,返回list實體類列表

public List ReieveList(string where) where T : class,new()
{
try
{
var className = (typeof(T)).Name;
var sql = string.Format("SELECT * FROM {0} {1}", className,where);
OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr);

DataSet ds = new DataSet();
da.Fill(ds);

var dt = ds.Tables[0];

var list = ConverterUtil.ConvertDataTableToList(dt);

return list;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return null;
}

}

//插入一條數據
public bool Insert(T entity) where T : class,new()
{

try
{
var type = typeof (T);
var className = type.Name;

var fields = "";
var values = "";

foreach (var property in type.GetProperties())
{
if (property.Name.Equals("ID")) continue;
fields += "," + property.Name;
var isNumStr = (property.PropertyType == typeof (double) ||
property.PropertyType == typeof (int))
? ""
: "'";
values += "," + isNumStr + property.GetValue(entity, null) + isNumStr;
}
fields = fields.Substring(1);
values = values.Substring(1);
var sql = string.Format("insert into {0}({1}) values ({2}) ", className,
fields, values);

OleDbDataAdapter da = new OleDbDataAdapter();


da.InsertCommand = new OleDbCommand(sql, conn);
da.InsertCommand.CommandText = sql;

conn.Open();
da.InsertCommand.ExecuteNonQuery();
conn.Close();

return true;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}

}

//更新實體類

public bool Update(T entity) where T : class,new()
{

try
{
var type = typeof(T);
var className = type.Name;

var values = "";

var id = "";
foreach (var property in type.GetProperties())
{
if (property.Name.Equals("ID"))
{
id = " where ID="+ property.GetValue(entity, null).ToString();
continue;
}
var isNumStr = (property.PropertyType == typeof(double) ||
property.PropertyType == typeof(int))
? ""
: "'";
values += "," +property.Name +"="+ isNumStr + property.GetValue(entity, null) + isNumStr;
}
values = values.Substring(1);
var sql = string.Format("update {0} set {1} {2}", className,
values,id);

OleDbDataAdapter da = new OleDbDataAdapter();

da.UpdateCommand = new OleDbCommand(sql, conn);
da.UpdateCommand.CommandText = sql;

conn.Open();
da.UpdateCommand.ExecuteNonQuery();
conn.Close();

return true;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}

}

//根據條件刪除數據
public bool Delete(string where)
{

try
{

var type = typeof(T);
var className = type.Name;
var sql = string.Format("delete from {0} {1}", className,
where);

OleDbDataAdapter da = new OleDbDataAdapter();

da.DeleteCommand = new OleDbCommand(sql, conn);
da.DeleteCommand.CommandText = sql;

conn.Open();
da.DeleteCommand.ExecuteNonQuery();
conn.Close();

return true;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}

}



//批量插入數據
public bool InsertList(List entitysList) where T : class,new()
{

try
{
var type = typeof (T);
var className = type.Name;

var fields = "";
var values = "";

foreach (var property in type.GetProperties())
{
if (property.Name.Equals("ID")) continue;
fields += "," + property.Name;
var isNumStr = (property.PropertyType == typeof (double) ||
property.PropertyType == typeof (int))
? ""
: "'";
values += ",?" ;
}
fields = fields.Substring(1);
values = values.Substring(1);
var sql = string.Format("insert into {0}({1}) values ({2}) ", className,
fields, values);

OleDbDataAdapter da = new OleDbDataAdapter();


da.InsertCommand = new OleDbCommand(sql, conn);
da.InsertCommand.CommandText = sql;

foreach (var property in type.GetProperties())
{
if (property.Name.Equals("ID")) continue;
var oleType = (property.PropertyType == typeof(double) ||
property.PropertyType == typeof(int))
? OleDbType.Integer
: OleDbType.VarChar;
da.InsertCommand.Parameters.Add(property.Name, oleType, int.MaxValue,
property.Name);
fields += "," + property.Name;

values += ",?";
}
var table = ConverterUtil.ConvertListToDataTable(entitysList);
table.TableName = className;
da.Update(table);

return true;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}

}



//這個方法是用來執行無返回結果的插入語句,如 insert select
public bool ExecuteInsertSql(string sql)
{

try
{
OleDbDataAdapter da = new OleDbDataAdapter();


da.InsertCommand = new OleDbCommand(sql, conn);
da.InsertCommand.CommandText = sql;

conn.Open();
da.InsertCommand.ExecuteNonQuery();
conn.Close();

return true;
}
catch (Exception e)
{

MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}

}

}

}



 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved