编程用户对数据库的操作封装
数据库接口工厂
public class dbFactory
{
public dbFactory()
{
}
public dbConnect CreatOracleConnect( string connectstring)
{
return new dbOracle(connectstring);
}
}
数据库接口抽象类
public abstract class dbConnect
{
public dbConnect() { }
public abstract void Connect();//创建数据库连接
public abstract void CheckConn();//数据连接检查
public abstract DataTable CreateDataTable(string strsql);//返回指定sql语句的datatable
public abstract void DeleteBySql(string strsql);//提交删除命令
public abstract void UpdateBySql(string strsql);//提交更新命令
public abstract void InsertBySql(string strsql);//提交插入命令
public abstract void FillArray(string strsql, string[] arr);//填充数组
public abstract void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql);//填充listbox
public abstract void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql);//填充gridview
public abstract void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql);
public abstract int GetNumber(string strsql);//返回数字型自段
public abstract string GetString(string strsql);//返回字符串字段
public abstract DataRow GetDataRow(string strsql);//得到一行数据
}
oracle数据库接口类:
public class dbOracle : dbConnect
{
private OracleConnection MyConn;//全局数据连接
private string MyConnectString;//全局连接字符串
private CFException myException = new CFException();
public dbOracle( string connectstring)
{
try
{
MyConn = new OracleConnection();
MyConnectString = connectstring;
//MyConn.ConnectionString = MyConnectString;
//MyConn.Open();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void Connect()//创建数据库连接
{
try
{
if (MyConn.State == ConnectionState.Closed || MyConn.State == ConnectionState.Broken)
{
MyConn.ConnectionString = MyConnectString;
MyConn.Open();
}
else
{
MyConn.Close();
MyConn.ConnectionString = MyConnectString;
MyConn.Open();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void CheckConn()//数据连接检查
{
try
{
if (this.MyConn.State == ConnectionState.Closed || this.MyConn.State == ConnectionState.Broken)
{
this.Connect();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override DataTable CreateDataTable(string strsql)
{
try
{
this.CheckConn();
OracleCommand Cmd = MyConn.CreateCommand();//sql查询命令对象
Cmd.CommandText = strsql;//sql命令付给MyOraCmd对象
OracleDataAdapter dabuff = new OracleDataAdapter();//建立适配器
DataTable tablebuff = new DataTable();
tablebuff.Rows.Clear();
dabuff.SelectCommand = Cmd;
tablebuff.BeginLoadData();
dabuff.Fill(tablebuff);
tablebuff.EndLoadData();
Cmd.Dispose();
dabuff.Dispose();
return tablebuff;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
//提交删除命令
public override void DeleteBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdDel = new OracleCommand();
OracleDataAdapter daDel = new OracleDataAdapter();
cmdDel.Connection = MyConn;
cmdDel.CommandText = strsql;
daDel.DeleteCommand = cmdDel;
daDel.DeleteCommand.ExecuteNonQuery();
cmdDel.Dispose();
daDel.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//提交更新命令
public override void UpdateBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdUpdate = new OracleCommand();
OracleDataAdapter daUpdate = new OracleDataAdapter();
cmdUpdate.Connection = MyConn;
cmdUpdate.CommandText = strsql;
daUpdate.UpdateCommand = cmdUpdate;
daUpdate.UpdateCommand.ExecuteNonQuery();
cmdUpdate.Dispose();
daUpdate.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//提交插入命令
public override void InsertBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdInsert = new OracleCommand();
OracleDataAdapter daInsert = new OracleDataAdapter();
cmdInsert.Connection = MyConn;
cmdInsert.CommandText = strsql;
daInsert.InsertCommand = cmdInsert;
daInsert.InsertCommand.ExecuteNonQuery();
cmdInsert.Dispose();
daInsert.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void FillArray(string strsql, string[] arr)
{
try
{
int i;
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
for (i = 0; i < tablebuff.Rows.Count; i++)
{
arr[i] = tablebuff.Rows[i].ToString();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//填充ListBox
public override void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
Listboxbuff.DataSource = tablebuff;
Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName ;
Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
Listboxbuff.DataBind();
}
catch(Exception ex)
{
myException.showMessage(ex);
}
}
//填充DropDownListBox
public override void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
Listboxbuff.DataSource = tablebuff;
Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName;
Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
Listboxbuff.DataBind();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//填充GridView
public override void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
GridViewbuff.DataSource = tablebuff;
GridViewbuff.DataBind();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//获得查询记录数
public override int GetNumber(string strsql)//通过select(*) from 得到结果
{
try
{
this.CheckConn();
int num;
OracleCommand Cmd = MyConn.CreateCommand();
Cmd.CommandText = strsql;
num =Convert.ToInt32(Cmd.ExecuteScalar());
return num;
}
catch (Exception ex)
{
myException.showMessage(ex);
return -1;
}
}
//获得查询字段值
public override string GetString(string strsql)
{
try
{
this.CheckConn();
string str;
OracleCommand Cmd = MyConn.CreateCommand();
Cmd.CommandText = strsql;
str = Convert.ToString(Cmd.ExecuteScalar());
return str;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
//得到sql指定的第一行
public override DataRow GetDataRow(string strsql)
{
try
{
DataRow rowbuff ;
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
rowbuff = tablebuff.Rows[0];
return rowbuff ;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
}
应用实例:一个可以显示oracle数据库中,指定方案(这里是CFGS)中的所有表及表中数据,一个ListBox1控件和一个GridView控件。
public partial class manager_tableshow : System.Web.UI.Page
{
private dbConnect myoracle;//数据库接口类
private CFException myException = new CFException();//异常处理类
protected void Page_Load(object sender, EventArgs e)
{
try
{
myoracle = (dbConnect)Session["myconnect"];
if (Page.IsPostBack == false)
{
this.showtablename();
}
else
{
this.showgrid();
}
}
catch (Exception ex)
{
myException.showMessage(ex, this.Page);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
this.showgrid();
}
private void showtablename()//列出所有表名
{
this.myoracle.FillListBox(this.ListBox1, "SELECT table_name,table_name FROM ALL_TABLES WHERE OWNER='CFGS' ");
}
private void showgrid()//显示表所有记录
{
try
{ string tablename;
tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
}
catch (Exception ex)
{
myException.showMessage(ex, this.Page);
}
}
protected void changepage(object sender, GridViewPageEventArgs e)//翻页事件
{
this.GridView1.PageIndex = e.NewPageIndex;
string tablename;
tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
}
}
数据库接口工厂
public class dbFactory
{
public dbFactory()
{
}
public dbConnect CreatOracleConnect( string connectstring)
{
return new dbOracle(connectstring);
}
}
数据库接口抽象类
public abstract class dbConnect
{
public dbConnect() { }
public abstract void Connect();//创建数据库连接
public abstract void CheckConn();//数据连接检查
public abstract DataTable CreateDataTable(string strsql);//返回指定sql语句的datatable
public abstract void DeleteBySql(string strsql);//提交删除命令
public abstract void UpdateBySql(string strsql);//提交更新命令
public abstract void InsertBySql(string strsql);//提交插入命令
public abstract void FillArray(string strsql, string[] arr);//填充数组
public abstract void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql);//填充listbox
public abstract void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql);//填充gridview
public abstract void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql);
public abstract int GetNumber(string strsql);//返回数字型自段
public abstract string GetString(string strsql);//返回字符串字段
public abstract DataRow GetDataRow(string strsql);//得到一行数据
}
oracle数据库接口类:
public class dbOracle : dbConnect
{
private OracleConnection MyConn;//全局数据连接
private string MyConnectString;//全局连接字符串
private CFException myException = new CFException();
public dbOracle( string connectstring)
{
try
{
MyConn = new OracleConnection();
MyConnectString = connectstring;
//MyConn.ConnectionString = MyConnectString;
//MyConn.Open();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void Connect()//创建数据库连接
{
try
{
if (MyConn.State == ConnectionState.Closed || MyConn.State == ConnectionState.Broken)
{
MyConn.ConnectionString = MyConnectString;
MyConn.Open();
}
else
{
MyConn.Close();
MyConn.ConnectionString = MyConnectString;
MyConn.Open();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void CheckConn()//数据连接检查
{
try
{
if (this.MyConn.State == ConnectionState.Closed || this.MyConn.State == ConnectionState.Broken)
{
this.Connect();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override DataTable CreateDataTable(string strsql)
{
try
{
this.CheckConn();
OracleCommand Cmd = MyConn.CreateCommand();//sql查询命令对象
Cmd.CommandText = strsql;//sql命令付给MyOraCmd对象
OracleDataAdapter dabuff = new OracleDataAdapter();//建立适配器
DataTable tablebuff = new DataTable();
tablebuff.Rows.Clear();
dabuff.SelectCommand = Cmd;
tablebuff.BeginLoadData();
dabuff.Fill(tablebuff);
tablebuff.EndLoadData();
Cmd.Dispose();
dabuff.Dispose();
return tablebuff;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
//提交删除命令
public override void DeleteBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdDel = new OracleCommand();
OracleDataAdapter daDel = new OracleDataAdapter();
cmdDel.Connection = MyConn;
cmdDel.CommandText = strsql;
daDel.DeleteCommand = cmdDel;
daDel.DeleteCommand.ExecuteNonQuery();
cmdDel.Dispose();
daDel.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//提交更新命令
public override void UpdateBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdUpdate = new OracleCommand();
OracleDataAdapter daUpdate = new OracleDataAdapter();
cmdUpdate.Connection = MyConn;
cmdUpdate.CommandText = strsql;
daUpdate.UpdateCommand = cmdUpdate;
daUpdate.UpdateCommand.ExecuteNonQuery();
cmdUpdate.Dispose();
daUpdate.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//提交插入命令
public override void InsertBySql(string strsql)
{
try
{
this.CheckConn();
OracleCommand cmdInsert = new OracleCommand();
OracleDataAdapter daInsert = new OracleDataAdapter();
cmdInsert.Connection = MyConn;
cmdInsert.CommandText = strsql;
daInsert.InsertCommand = cmdInsert;
daInsert.InsertCommand.ExecuteNonQuery();
cmdInsert.Dispose();
daInsert.Dispose();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
public override void FillArray(string strsql, string[] arr)
{
try
{
int i;
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
for (i = 0; i < tablebuff.Rows.Count; i++)
{
arr[i] = tablebuff.Rows[i].ToString();
}
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//填充ListBox
public override void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
Listboxbuff.DataSource = tablebuff;
Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName ;
Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
Listboxbuff.DataBind();
}
catch(Exception ex)
{
myException.showMessage(ex);
}
}
//填充DropDownListBox
public override void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
Listboxbuff.DataSource = tablebuff;
Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName;
Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
Listboxbuff.DataBind();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//填充GridView
public override void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql)
{
try
{
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
GridViewbuff.DataSource = tablebuff;
GridViewbuff.DataBind();
}
catch (Exception ex)
{
myException.showMessage(ex);
}
}
//获得查询记录数
public override int GetNumber(string strsql)//通过select(*) from 得到结果
{
try
{
this.CheckConn();
int num;
OracleCommand Cmd = MyConn.CreateCommand();
Cmd.CommandText = strsql;
num =Convert.ToInt32(Cmd.ExecuteScalar());
return num;
}
catch (Exception ex)
{
myException.showMessage(ex);
return -1;
}
}
//获得查询字段值
public override string GetString(string strsql)
{
try
{
this.CheckConn();
string str;
OracleCommand Cmd = MyConn.CreateCommand();
Cmd.CommandText = strsql;
str = Convert.ToString(Cmd.ExecuteScalar());
return str;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
//得到sql指定的第一行
public override DataRow GetDataRow(string strsql)
{
try
{
DataRow rowbuff ;
DataTable tablebuff;
tablebuff = this.CreateDataTable(strsql);
rowbuff = tablebuff.Rows[0];
return rowbuff ;
}
catch (Exception ex)
{
myException.showMessage(ex);
return null;
}
}
}
应用实例:一个可以显示oracle数据库中,指定方案(这里是CFGS)中的所有表及表中数据,一个ListBox1控件和一个GridView控件。
public partial class manager_tableshow : System.Web.UI.Page
{
private dbConnect myoracle;//数据库接口类
private CFException myException = new CFException();//异常处理类
protected void Page_Load(object sender, EventArgs e)
{
try
{
myoracle = (dbConnect)Session["myconnect"];
if (Page.IsPostBack == false)
{
this.showtablename();
}
else
{
this.showgrid();
}
}
catch (Exception ex)
{
myException.showMessage(ex, this.Page);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
this.showgrid();
}
private void showtablename()//列出所有表名
{
this.myoracle.FillListBox(this.ListBox1, "SELECT table_name,table_name FROM ALL_TABLES WHERE OWNER='CFGS' ");
}
private void showgrid()//显示表所有记录
{
try
{ string tablename;
tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
}
catch (Exception ex)
{
myException.showMessage(ex, this.Page);
}
}
protected void changepage(object sender, GridViewPageEventArgs e)//翻页事件
{
this.GridView1.PageIndex = e.NewPageIndex;
string tablename;
tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
}
}