Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Code Snippets » C# Syntax »
Mysql DAL
|
Hey, many new developers who are using the combination of mysql + .net, this post is for them, i develop this DAL for mysql, this take lot of time when development first time, but i dnt want other developer waste their time in development of DAL, Every function written in DAL is very simple and understandable and reusable, Fill grid, fill dropdownlist, return datatable, return dataset, execute delete query, update query, get last inserted ID, etc..... these functionality is performed by this DAL, many be some of function is not usable for you because i have little different requirement then other, please feel free to ask about every single function, remember one thing more, this DAL is base on queries not store procedures, Include Mysql DLL to use this DAL
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;
using MySql.Data.MySqlClient;
//Created By Nisar Ahmad
//01/03/2009
namespace FidesTech
{
///
/// Summary description for DBConnection
///
///
public class DBConnection : System.Web.UI.Page
{
MySqlDataAdapter dAdapter = new MySqlDataAdapter();
MySqlConnection conn = new MySqlConnection();
DataSet ds = new DataSet();
ErrorHandler err = new ErrorHandler();
public DBConnection()
{
//
// TODO: Add constructor logic here
//
}
///
/// For open connection
///
public void openConn()
{
try
{
string constr = ConfigurationManager.AppSettings["connectionString"];
conn.ConnectionString = constr;
conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException mySqlEx)
{
err.WriteError(mySqlEx.Message.ToString());
}
catch (System.Web.HttpException httpEx)
{
err.WriteError(httpEx.Message.ToString());
}
}
///
/// For close connection
///
public void closeConn()
{
try
{
conn.Close();
}
catch (Exception ex)
{
}
}
//public void getData(string query)
//{}
///
/// Common funtion to execute all queries
///
///
//You just pass
public void ExecuteQuery(string query)
{
try
{
openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
closeConn();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
err.WriteError(ex.Message.ToString());
return;
}
}
public int SaveDB(string query)
{
int BinaryImage = 0;
try
{
openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd = conn.CreateCommand();
cmd.CommandText = query;
BinaryImage=cmd.ExecuteNonQuery();
closeConn();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
err.WriteError(ex.Message.ToString()); ;
}
return BinaryImage;
}
/////////////
public int getImg(string query)
{
int BinaryImage = 0;
try
{
openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd = conn.CreateCommand();
cmd.CommandText = query;
BinaryImage = cmd.ExecuteNonQuery();
closeConn();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
}
return BinaryImage;
}
///
/// Return DataTable
///
///
///
public DataTable ReturnTable(string Query)
{
DataTable dt = new DataTable();
try
{
openConn();
DataSet ds = new DataSet();
MySqlDataAdapter ad = new MySqlDataAdapter(Query, conn);
ad.Fill(dt);
closeConn();
}
catch (Exception ex)
{
//return;
}
return dt;
}
///
/// Return Last Inserted ID
///
///
///
public string setDataID(string query)
{
string test = "";
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd = conn.CreateCommand();
cmd.CommandText = query;
MySqlCommand lastId;
MySqlDataReader lid = null;
lastId = new MySqlCommand();
lastId.Connection = conn;
lastId.CommandText = ("SELECT LAST_INSERT_ID() as id");
try
{
this.openConn();
cmd.ExecuteNonQuery();
//lid = lastId.ExecuteReader(CommandBehavior.CloseConnection);
test = lastId.ExecuteScalar().ToString();
//test = lid[0].ToString();
//test = Convert.ToInt32( lid.ToString());
this.closeConn();
}
//cmd.ExecuteNonQuery();
///cmd.CommandText = "SELECT @@IDENTITY AS 'LastID'";
//test = Convert.ToInt32(cmd.CommandText);
//closeConn();
//}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
err.WriteError(ex.Message.ToString());
}
return test;
}
///
/// Delete Data from grid
///
///
///
///
///
public void DeleteData(String tblname, String pk, GridView rowgridview, GridViewDeleteEventArgs e)
{
int id = int.Parse(rowgridview.DataKeys[e.RowIndex].Value.ToString());
string query = "DELETE FROM " + tblname + " WHERE " + pk + "=" + id;
this. ExecuteQuery (query);
//Response.Redirect("~/fm_newRole.aspx");
}
///
/// Edit Grid(Only MS gridControl)
///
///
///
///
///
///
public string[] EditRecord(String tblname, String pk, GridView rowgridview, GridViewEditEventArgs e)
{
String query = "";
//String[] ret_val = new String[20];
try
{
int id = int.Parse(rowgridview.DataKeys[e.NewEditIndex].Value.ToString());
//hdn_val.Value = (string)id;
//String query = "select Password from tbl_user where UserId="+id;
query = "SELECT * FROM " + tblname + " WHERE " + pk + " = " + id;
//string pass = getPassword(query);
//ret_val = getEditableData(query);
//string str_id = id.ToString();
//ret_val[0] = pass;
//ret_val[1] = str_id;
}
catch (MySql.Data.Types.MySqlConversionException mysqlEx)
{
}
//return ret_val;
//string query = "DELETE FROM " + tblname + " WHERE " + pk + "=" + id;
//this.setData(query);
//Response.Redirect("~/fm_newRole.aspx");
return getEditableData(query);
}
///
/// Fill Data in GridView
///
///
///
public void GridShow(String query, GridView gridview)//this function is for MS GridView
{
DataSet ds = GetData(query);
if (ds.Tables.Count > 0)
{
gridview.DataSource = ds;
gridview.DataBind();
}
else
{
//Response.Write("Unable to connect to the database.");
}
}
///
/// Return DataSet against query
///
///
///
DataSet GetData(String queryString)
{
String connectionString = string.Empty;
// Retrieve the connection string stored in the Web.config file.
connectionString = ConfigurationManager.AppSettings["connectionString"];
DataSet ds = new DataSet();
try
{
// Connect to the database and run the query.
conn = new MySqlConnection(connectionString);
dAdapter = new MySqlDataAdapter(queryString, conn);
// Fill the DataSet.
dAdapter.Fill(ds);
}
catch (Exception ex)
{
err.WriteError(ex.Message.ToString());
}
return ds;
}
///
/// Fill Dropdownlist
///
///
///
///
///
///
public void ddlist(string strquery, DropDownList ddl, string datavalue, string textvalue, DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
ddl.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strquery;
da.SelectCommand = cmd;
da.Fill(ds);
//da.Fill(ds=new DataSet());
datavalue = ds.Tables[0].Rows[0][0].ToString();
ddl.DataSource = ds.Tables[0].DefaultView;
ddl.DataValueField = datavalue;
ddl.DataTextField = textvalue;
ddl.DataBind();
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
finally
{
this.closeConn();
}
}
///
/// Fill dropdownlist
///
///
///
///
public void fillList(string strQry, ref DropDownList list, ref DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
list.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strQry;
da.SelectCommand = cmd;
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list.Items.Add(new ListItem(ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][0].ToString()));
}
}
catch (Exception ex)
{
}
finally
{
this.closeConn();
}
}// End Function
///
/// Fill ListBox
///
///
///
///
public void fillListBOX(string strQry, ref ListBox list, ref DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
list.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strQry;
da.SelectCommand = cmd;
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list.Items.Add(new ListItem(ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][0].ToString()));
}
}
catch (Exception ex)
{
}
finally
{
this.closeConn();
}
}
public void fillListCustom(string strQry, ref DropDownList list, ref DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
list.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strQry;
da.SelectCommand = cmd;
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list.Items.Add(new ListItem(ds.Tables[0].Rows[i][1].ToString() + " [€ " + ds.Tables[0].Rows[i][2].ToString() + "]", ds.Tables[0].Rows[i][0].ToString()));
}
}
catch (Exception ex)
{
}
finally
{
this.closeConn();
}
}// End Function
///
/// Fill Dropdownlist with line
///
///
///
///
public void fillList_line(string strQry, ref DropDownList list, ref DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
list.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strQry;
da.SelectCommand = cmd;
da.Fill(ds);
list.Items.Add(new ListItem("--------------------------", "0"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list.Items.Add(new ListItem(ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][0].ToString()));
}
}
catch (Exception ex)
{
}
finally
{
this.closeConn();
}
}// End Function
///
/// Fill dropdownlist with the text ALL
///
///
///
///
public void fillList_line_all(string strQry, ref DropDownList list, ref DataSet ds)
{
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
ds = new DataSet();
list.Items.Clear();//Clear Items if any
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = strQry;
da.SelectCommand = cmd;
da.Fill(ds);
list.Items.Add(new ListItem("---------- All -----------", "0"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list.Items.Add(new ListItem(ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][0].ToString()));
}
}
catch (Exception ex)
{
}
finally
{
this.closeConn();
}
}// End Function
///
/// Get the Maximun ID of table
///
///
///
public int getMaxId(string query)
{
DataSet ds = new DataSet();
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
int purchaseid = 0;
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
//the indexer of the DataRow object is the most important
//thing here. it's just like oRs.Fields(0).Value in regular ADO:
//the first field is ordinal 0, the second field is ordinal 1.
//The indexer returns object that has a specified type. You can
//cast the object to that type.
purchaseid = Convert.ToInt32(drow[0]);
}
//Response.Write("datavalue: "+purchaseid);
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
return purchaseid;
}
///
/// Check the valid user
///
///
///
public int isValidUser(string query)
{
DataSet ds = new DataSet();
openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
int userid = 0;
//ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
userid = (int)drow[0];
}
}
catch (Exception ex)
{
//Response.Write("Exception: " + ex.Message.ToString());
}
//closeConn();
return userid;
}
///
/// Get UserRole
///
///
///
public String getRole(String query)
{
DataSet ds = new DataSet();
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
String role = "";
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
role = (String)drow[0];
}
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
return role;
}
///
/// Get Password
///
///
///
public string getPassword(String query)
{
DataSet ds = new DataSet();
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
string pass = "";
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
try
{
pass = (string)drow[0];
}
catch (System.IndexOutOfRangeException Ex)
{
}
catch (System.InvalidCastException icEx)
{
}
}
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
return pass;
}
///
/// retuns an array containing user data
///
///
///
public string[] getEditableData(String query)
{
DataSet ds = new DataSet();
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
//String[] u_data = new String[20];
String[] u_data = null;
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
try
{
int cols = dt.Columns.Count;
u_data = new String[cols];
for (int i = 0; i < cols; i++)
{
u_data[i] = drow[i].ToString();
//u_data[0] = drow[0].ToString();
//u_data[1] = drow[1].ToString();
//u_data[2] = drow[2].ToString();
//u_data[3] = drow[3].ToString();
//u_data[4] = drow[4].ToString();
//u_data[5] = drow[5].ToString();
//u_data[6] = drow[6].ToString();
//u_data[7] = drow[7].ToString();
//u_data[8] = drow[8].ToString();
//u_data[9] = drow[9].ToString();
//u_data[10] = drow[10].ToString();
//u_data[11] = drow[11].ToString();
//u_data[12] = drow[12].ToString();
//u_data[13] = drow[13].ToString();
//u_data[14] = drow[14].ToString();
//u_data[15] = drow[15].ToString();
//u_data[16] = drow[16].ToString();
////u_data[17] = drow[17].ToString();
////u_data[18] = drow[18].ToString();
//u_data[19] = drow[19].ToString();
}
}
catch (System.IndexOutOfRangeException Ex)
{
}
catch (System.InvalidCastException icEx)
{
}
catch (MySql.Data.Types.MySqlConversionException mysqlEx)
{
}
}
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
return u_data;
}
//function to get profit/loss
public string[] get2Cols(String query)
{
DataSet ds = new DataSet();
this.openConn();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
String[] cols = new String[2];
ds.Clear();
try
{
ds.Clear();
cmd = conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
try
{
cols[0] = drow[0].ToString();
cols[1] = drow[1].ToString();
}
catch (System.IndexOutOfRangeException Ex)
{
//cols[0] = null;
//cols[1] = null;
}
catch (System.InvalidCastException icEx)
{
//cols[0] = null;
//cols[1] = null;
}
}
}
catch (System.Web.HttpException httpEx)
{
//Response.Write("Exception: " + httpEx.Message.ToString());
}
return cols;
}
}
}
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|