public DbConnection GetConnection(string sConnectionString, DBEngineType DBEngine)
{
try
{
switch (DBEngine)
{
case DBEngineType.OLEDB:
//objCon = new OleDb.OleDbConnection(sConnectionString);
objCon = new OleDbConnection(sConnectionString);
break;
case DBEngineType.ODBC:
//objCon = new Odbc.OdbcConnection(sConnectionString);
objCon = new OdbcConnection(sConnectionString);
break;
case DBEngineType.SQL:
//objCon = new SqlClient.SqlConnection(sConnectionString);
objCon = new SqlConnection(sConnectionString);
break;
}
// objCon.Open()
}
catch (Exception ex)
{
//objLog.PersistException("DataAccess", "GetConnection()", ex)
// Logger.logException("DataAccess", "GetConnection", "CS:" & sConnectionString & ", Message:" & ex.Message)
}
return(objCon);
}
protected void Page_Load(object sender, EventArgs e)
{
string Query = string.Empty;
string Filter = Request.QueryString.Get("Filter");
string Year1 = Request.QueryString.Get("Year1");
hdnLookupName.Value = Request.QueryString.Get("Lookup");
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
DBEngineType _eDBType = new DBEngineType();
clsDBAccess obj_clsDBAccess = new clsDBAccess(conn, _eDBType);
switch (hdnLookupName.Value)
{
case "Item":
DataTable dt_Items = obj_GeneralFunction.GetAllItems();
this.Title = "Item Lookup";
//grid_Lookup.DataSource = ObjDB_MLTNT.GetDataTable(Query);
grid_Lookup.DataSource = dt_Items;
break;
default:
break;
}
obj_clsDBAccess.DisposeConnection();
}
public clsDBAccess(string sConnectionString, DBEngineType DBEngine)
{
DBType = DBEngine;
objCon = GetConnection(sConnectionString, DBEngine);
conStr = sConnectionString;
objSqlCon = new SqlConnection(conStr);
}
protected void grid_ItemDetail_ItemCreated(object sender, GridItemEventArgs e)
{
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
DBEngineType _eDBType = new DBEngineType();
clsDBAccess obj_clsDBAccess = new clsDBAccess(conn, _eDBType);
if (e.Item is GridEditableItem && e.Item.IsInEditMode)
{
int RowID = e.Item.DataSetIndex;
GridEditableItem edititem = (GridEditableItem)e.Item;
DataTable dt_ItemLines = (DataTable)Session["dt_ItemLines"];
RadTextBox tbItemCode = (RadTextBox)edititem["ItemCode"].FindControl("tbItemCode");
tbItemCode.AutoPostBack = true;
tbItemCode.TextChanged += new System.EventHandler(this.tbItemCode_TextChanged);
RadNumericTextBox tbQuantity = (RadNumericTextBox)edititem["Quantity"].FindControl("tbQuantity");
tbQuantity.AutoPostBack = true;
tbQuantity.TextChanged += new System.EventHandler(this.tbQuantity_TextChanged);
RadNumericTextBox tbAmount = (RadNumericTextBox)edititem["Amount"].FindControl("tbAmount");
tbAmount.AutoPostBack = true;
tbAmount.TextChanged += new System.EventHandler(this.tbAmount_TextChanged);
if (RowID > -1)
{
if (dt_ItemLines.Rows[RowID]["ItemCode"].ToString() != "")
{
tbItemCode.ShowButton = false;
tbItemCode.Enabled = false;
}
}
}
obj_clsDBAccess.DisposeConnection();
}
public DataSource(string table_name, string connection_string, DBEngineType tip, bool s_auto, bool u_auto, bool d_auto, bool i_auto, byte r)
{
select = " "; insert = " "; update = " "; delete = " ";
StringBuilder str = new StringBuilder();
StringBuilder str_param = new StringBuilder();
identity = new StringBuilder();
identities = new ArrayList();
identities.Clear();
dbtype = tip;
con_string = connection_string;
var db = new Database(tip);
db.con_string = connection_string;
if (tip == DBEngineType.MsSql)
db.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and lower(sysobjects.xtype) = 'u'and sysobjects.name = @t_name order by syscolumns.name";
else if (tip == DBEngineType.MySql)
db.text = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @t_name";
else if (tip == DBEngineType.DB2)
db.text = "select colname from syscat.columns where tabname= @t_name";
db.add_parameter("@t_name", table_name);
var db2 = new Database(tip);
db2.con_string = connection_string;
if (tip == DBEngineType.MsSql)
db2.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and syscolumns.colstat=1 and lower(sysobjects.xtype) = 'u' and sysobjects.name = @t_name order by syscolumns.name";
else if (tip == DBEngineType.MySql)
db2.text = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @t_name and COlUMN_KEY='PRI'";
else if (tip == DBEngineType.DB2)
db2.text = "select colname from syscat.columns where tabname= @t_name and (KEYSEQ is not null)";
db2.add_parameter("@t_name", table_name);
foreach (DataRow dare in db2.table.Rows)
{
identities.Add(dare[0].ToString());
if (identity.Length == 0)
identity.Append(String.Format("{0}=@{0}", dare[0].ToString()));
else
identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString()));
}
if (identity.Length > 0)
delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString());
else
{
foreach (DataRow dare in db.table.Rows)
{
identities.Add(dare[0].ToString());
if (identity.Length == 0)
identity.Append(String.Format("{0}=@{0}", dare[0].ToString()));
else
identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString()));
}
}
delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString());
foreach (DataRow dr in db.table.Rows)
{
if (str.Length == 0)
{
str.Append(dr[0]);
}
else
str.Append("," + dr[0]);
}
select = String.Format("select {0} from {1}", str.ToString(), table_name);
str_param = new StringBuilder();
str = new StringBuilder();
foreach (DataRow dr in db.table.Rows)
{
if (!identities.Contains(dr[0].ToString()))
{
if (str.Length == 0)
{
str.Append(dr[0]);
str_param.Append(String.Format("@{0}", dr[0]));
}
else
{
str.Append("," + dr[0]);
str_param.Append(String.Format(",@{0}", dr[0]));
}
}
}
columns = str.ToString();
insert = (String.Format("insert into {1} ({0}) values({2})", str.ToString(), table_name, str_param.ToString()));
str_param = new StringBuilder();
str = new StringBuilder();
foreach (DataRow dr in db.table.Rows)
{
if (!identities.Contains(dr[0].ToString()))
{
if (str_param.Length == 0)
{
str_param.Append(String.Format("{0}=@{0}", dr[0]));
}
else
{
str_param.Append(String.Format(",{0}=@{0}", dr[0]));
}
}
}
update = (String.Format("update {1} set {2} where {0}", identity, table_name, str_param.ToString()));
//else
// update = (String.Format("update {1} set {2} ", table_name, str_param.ToString()));
}
public DataSource(string table_name, string cstr_name, DBEngineType tip, bool s_auto, bool u_auto, bool d_auto, bool i_auto)
{
select = " "; insert = " "; update = " "; delete = " ";
StringBuilder str = new StringBuilder();
StringBuilder str_param = new StringBuilder();
identity = new StringBuilder();
identities = new ArrayList();
var db = new Database(DBEngineType.MsSql);
identities.Clear();
db.con_name = cstr_name;
db.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and lower(sysobjects.xtype) = 'u'and sysobjects.name = @t_name order by syscolumns.name";
db.add_parameter("@t_name", table_name);
var db2 = new Database(DBEngineType.MsSql);
db2.con_name = cstr_name;
db2.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and syscolumns.colstat=1 and lower(sysobjects.xtype) = 'u' and sysobjects.name = @t_name order by syscolumns.name";
db2.add_parameter("@t_name", table_name);
foreach (DataRow dare in db2.table.Rows)
{
identities.Add(dare[0].ToString());
if (identity.Length == 0)
identity.Append(String.Format("{0}=@{0}", dare[0].ToString()));
else
identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString()));
}
if (identity.Length > 0)
delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString());
else
{
foreach (DataRow dare in db.table.Rows)
{
identities.Add(dare[0].ToString());
if (identity.Length == 0)
identity.Append(String.Format("{0}=@{0}", dare[0].ToString()));
else
identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString()));
}
}
delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString());
foreach (DataRow dr in db.table.Rows)
{
if (str.Length == 0)
{
str.Append(dr[0]);
}
else
str.Append("," + dr[0]);
}
select = String.Format("select {0} from {1}", str.ToString(), table_name);
str_param = new StringBuilder();
str = new StringBuilder();
foreach (DataRow dr in db.table.Rows)
{
if (!identities.Contains(dr[0].ToString()))
{
if (str.Length == 0)
{
str.Append(dr[0]);
str_param.Append(String.Format("@{0}", dr[0]));
}
else
{
str.Append("," + dr[0]);
str_param.Append(String.Format(",@{0}", dr[0]));
}
}
}
columns = str.ToString();
insert = (String.Format("insert into {1} ({0}) values({2})", str.ToString(), table_name, str_param.ToString()));
str_param = new StringBuilder();
str = new StringBuilder();
foreach (DataRow dr in db.table.Rows)
{
if (!identities.Contains(dr[0].ToString()))
{
if (str_param.Length == 0)
{
str_param.Append(String.Format("{0}=@{0}", dr[0]));
}
else
{
str_param.Append(String.Format(",{0}=@{0}", dr[0]));
}
}
}
update = (String.Format("update {1} set {2} where {0}", identity, table_name, str_param.ToString()));
//else
// update = (String.Format("update {1} set {2} ", table_name, str_param.ToString()));
}
public Database(DBEngineType tip)
{
_dbtip = tip;
if (tip == DBEngineType.MsSql)
{
_cmd = new SqlCommand();
_cmd.Connection = con as SqlConnection;
_cmd.CommandText = text;
_cmd.CommandType = cmd_type;
_da = new SqlDataAdapter();
}
else if (tip == DBEngineType.MySql)
{
__cmd = new MySqlCommand();
__cmd.Connection = mycon as MySqlConnection;
__cmd.CommandText = text;
__cmd.CommandType = cmd_type;
__da = new MySqlDataAdapter();
}
else if (tip == DBEngineType.Oledb)
{
____cmd = new OleDbCommand();
____cmd.Connection = OleDbcon as OleDbConnection;
____cmd.CommandText = text;
____cmd.CommandType = cmd_type;
____da = new OleDbDataAdapter();
}
_dset = new DataSet();
_text = "";
}
public void con_open(object __con, DBEngineType tip)
{
try
{
if (tip == DBEngineType.MsSql)
{
if ((__con as SqlConnection).State != ConnectionState.Open)
(__con as SqlConnection).Open();
}
else if (tip == DBEngineType.MySql)
{
if ((__con as MySqlConnection).State != ConnectionState.Open)
(__con as MySqlConnection).Open();
}
else if (tip == DBEngineType.Oledb)
{
if ((__con as OleDbConnection).State != ConnectionState.Open)
(__con as OleDbConnection).Open();
}
}
catch (Exception x)
{
throw new Exception("Bağlantı açılamıyor\r\n " + x.ToString());
}
}
public void con_open(DBEngineType tip)
{
try
{
if (tip == DBEngineType.MsSql)
{
if (con.State != ConnectionState.Open)
con.Open();
}
else
if (tip == DBEngineType.MySql)
{
if (mycon.State != ConnectionState.Open)
mycon.Open();
}
else
if (tip == DBEngineType.Oledb)
{
if (OleDbcon.State != ConnectionState.Open)
OleDbcon.Open();
}
}
catch
{
throw new Exception("Bağlantı açılamıyor");
}
}
public void con_close(object __con, DBEngineType tip)
{
try
{
if (tip == DBEngineType.MsSql)
{
if ((__con as SqlConnection).State != ConnectionState.Closed)
(__con as SqlConnection).Close();
}
else if (tip == DBEngineType.MySql)
{
if ((__con as MySqlConnection).State != ConnectionState.Closed)
(__con as MySqlConnection).Close();
}
else if (tip == DBEngineType.Oledb)
{
if ((__con as OleDbConnection).State != ConnectionState.Closed)
(__con as OleDbConnection).Close();
}
}
catch
{
throw new Exception("Bağlantı kapatılamıyor");
}
}