我的MySql 数据访问层

2021-11-21 03:52:04

如今，基于.net的一些ORM之类的数据访问层的东东好象挺火的(LINQ之类的)，但是鄙人却还对他的好还没有彻底看出个端倪来，用在鄙人的项目中总感觉有些杀鸡焉用牛刀的感觉。

我也看过金色海洋Blog里的关于数据访问层的一些代码，很谦虚的说，应该比我的要好，可是却总让人感觉很烦琐，所以我并没有改用他的，而是在他的想法上自己也凑合着写了一个。^ - ^

我把它放在了项目的 App_Code 文件夹中，并通过 using MySql.Data.MySqlClient; 来引用MySql的数据库引擎DLL文件就OK啦！

using MySql.Data.MySqlClient;

//比如，我要得到GridView的数据集

string Myselect = "select cpuinfo,count(cpuinfo)'number' from HARDWAREINFO";

Myselect+= "where orgid like '" + orgid + "' group by cpuinfo order by number desc";

myDataTable cesTable = new myDataTable(Myselect);

cpu_GridView.DataSource = cesTable.myTable();

cpu_GridView.DataBind();

//比如，我要得到执行数据库操作

protected void InsertView(string suborgid, string orgname, string orgtype, string abs)

{

string norgid = GetOrgid();

string Insertsql = "insert into ORGINFO values('" + orgid + "', '" + orgname + "', '" + norgid + "', '" + orgtype + "', '" + abs + "')";

myDataTable myTable = new myDataTable(Insertsql);

try

{

myTable.mysql_EXE();
我的MySql 数据访问层

}

catch (Exception e)

{

}

附：
myDataTable.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//Myself using
using MySql.Data.MySqlClient;
using System.Web.Configuration;
/**//// <summary>
/// myDataTable 的摘要说明
/// </summary>
public class myDataTable
{
public string Client_Str;

public myDataTable(string My_str)
{
//
// TODO: 在此处添加构造函数逻辑
//
Client_Str=My_str; //通过构造函数传递strng给class类的全局变量strng;
}
public void mysql_EXE() //执行SQL但不返回数据
{
string myConn_str = WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
MySqlConnection myConn = new MySqlConnection(myConn_str);
MySqlCommand myComm = new MySqlCommand(Client_Str);
//myConn.Open();
myComm.Connection = myConn;
try
{
if (myComm.Connection.State.ToString() == "Closed") myComm.Connection.Open();
myComm.ExecuteNonQuery();
}
catch(Exception ex)
{
Session["Error"] =ex.ToString();
Response.Redirect("error.aspx");
}
finally
{
myComm.Dispose();
myComm.Connection.Close();
myConn.Dispose();
myConn.Close();
}
//myConn.Close();
}
public DataTable myTable() //执行SQL并返回DataTable数据
{
string myConn_str = WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
MySqlConnection myConn = new MySqlConnection(myConn_str);
MySqlCommand myComm = new MySqlCommand(Client_Str);
//myConn.Open();
myComm.Connection = myConn;
try
{
if (myComm.Connection.State.ToString() == "Closed") myComm.Connection.Open();
myComm.ExecuteNonQuery();
}
catch(Exception ex)
{
Session["Error"] =ex.ToString();
Response.Redirect("error.aspx");
}
finally
{
myComm.Dispose();
myComm.Connection.Close();
myConn.Dispose();
myConn.Close();
}
//myConn.Close();
MySqlDataAdapter myAdapter = new MySqlDataAdapter(myComm);
DataTable table = new DataTable();
myAdapter.Fill(table);
return table;
}
public void Delete_procedure() //“删除”的存储过程
{
string str_orgid = Client_Str; //获得orgid
string myConn_str = WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
MySqlConnection myConn = new MySqlConnection(myConn_str);
MySqlCommand myComm = new MySqlCommand("DELETEDB", myConn);//(Client_Str);
//myComm.Connection = myConn;
try
{
if (myComm.Connection.State.ToString() == "Closed") myComm.Connection.Open();
myComm.CommandType = CommandType.StoredProcedure;
MySqlParameter myParameter;
myParameter = new MySqlParameter("?m_orgid", MySqlDbType.String);
myParameter.Value = str_orgid;
myParameter.Direction = ParameterDirection.Input;
myComm.Parameters.Add(myParameter);

//myComm.CommandText = "DELETEDB"; //存储过程名
//myComm.Parameters.Add("m_orgid", str_orgid);
myComm.ExecuteNonQuery();
}
catch(Exception ex)
{
Session["Error"] =ex.ToString();
Response.Redirect("error.aspx");
}
finally
{
myComm.Dispose();
myComm.Connection.Close();
myConn.Dispose();
myConn.Close();
}
}
public int My_ExecuteScalar() //执行SQL并返回int记录数
{
string str_orgid = Client_Str; //获得orgid
int Re_result = 0;
string myConn_str = WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
MySqlConnection myConn = new MySqlConnection(myConn_str);
MySqlCommand myComm = new MySqlCommand(Client_Str,myConn);
try
{
if (myComm.Connection.State.ToString() == "Closed") myComm.Connection.Open();
//cmd.CommandText = "select count(*) from region";
//Int32 count = (int32)cmd.ExecuteScalar();
Re_result = Convert.ToInt32(myComm.ExecuteScalar());
}
catch(Exception ex)
{
Session["Error"] =ex.ToString();
Response.Redirect("error.aspx");
}
finally
{
myComm.Dispose();
myComm.Connection.Close();
myConn.Dispose();
myConn.Close();
}
return Re_result;
}
}

本文转自钢钢博客园博客，原文链接：http://www.cnblogs.com/xugang/archive/2007/10/04/914056.html，如需转载请自行联系原作者

码农公寓

相关文章