Cybercrux

Everything is achievable through technology

ACCESS DAL

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Configuration;

public class ACCESSDB
{
private static OleDbConnection CONDB = new OleDbConnection();

private static OleDbDataAdapter ADPTR;

public static DataTable DT;

public static int LoginId=0;

public static void Connect()
{
if (CONDB.State != ConnectionState.Open)
{
string strConnection = ConfigurationManager.ConnectionStrings[“DatabaseConnection”].ConnectionString;
CONDB = new OleDbConnection(strConnection);
CONDB.Open();
}
}

public static void Close()
{
try
{
CONDB.Close();
}
catch(Exception ex)
{
#if DEBUG
throw ex;
#endif
}
}

public static OleDbConnection GetConnect()
{
return CONDB;
}

public static void ExecuteQuery(string QueryString)
{
Connect();
OleDbCommand cmd = new OleDbCommand(QueryString, CONDB);
cmd.ExecuteNonQuery();
Close();
cmd.Dispose();
}

#region//next number

public static int NextNum(string TableName, string FieldName, int StartingNum)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select MAX(” + FieldName + “) from ” + TableName, CONDB);
object ExeReturn = cmd.ExecuteScalar();
Close();
if (ExeReturn.ToString() != “”)
{
Int32 dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();
return dMax < StartingNum ? StartingNum : dMax + 1;
}
else
{
cmd.Dispose();
return StartingNum;
}

}

public static int NextNum(string TableName, string FieldName,string Condition, int StartingNum)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select MAX(” + FieldName + “) from ” + TableName+” where “+Condition, CONDB);
object ExeReturn = cmd.ExecuteScalar();
Close();
if (ExeReturn.ToString() != “”)
{
Int32 dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();
return dMax < StartingNum ? StartingNum : dMax + 1;
}
else
{
cmd.Dispose();
return StartingNum;
}

}

public static int NextNum(string TableName, string FieldName)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select MAX(” + FieldName + “) from ” + TableName, CONDB);
object ExeReturn = cmd.ExecuteScalar();
Close();
Int32 dMax=0;
if (ExeReturn.ToString() != “”)
{
dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();

}
return dMax;

}

#endregion

public static int FieldCount(string FieldName, string TableName, string Condition)
{ Connect();
OleDbCommand cmd = new OleDbCommand(“SELECT COUNT(” + FieldName + “) FROM ” + TableName + ” WHERE ” + Condition, CONDB);
int count = (int)cmd.ExecuteScalar();
Close();
return count;

}

public static DataTable Query(string QueryString)
{
Connect();
DataTable DT = new DataTable();
ADPTR = new OleDbDataAdapter(QueryString, CONDB);
ADPTR.Fill(DT);
Close();
return DT;
}

public static DataSet Query(string QueryString, string Table)
{
DataSet DS = new DataSet();
DS.Tables.Clear();
ADPTR = new OleDbDataAdapter(QueryString, CONDB);
ADPTR.Fill(DS, Table);
return DS;
}

public static bool RowExists(string TableName, string Condition)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select * from ” + TableName + ” where ” + Condition, CONDB);
if (cmd.ExecuteScalar() == null)
{ return false; }
{ return true;}

}

public static object GetField(string FieldName, string TableName, string Condition)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select ” + FieldName + ” from ” + TableName + ” where ” + Condition, CONDB);
return cmd.ExecuteScalar();

}

public static object GetField(string FieldName, string TableName)
{
Connect();
OleDbCommand cmd = new OleDbCommand(“select ” + FieldName + ” from ” + TableName , CONDB);
return cmd.ExecuteScalar();

}

public static void InsertInto(string tableName, string fieldList, params string[] values)
{
StringBuilder qry = new StringBuilder(“insert into ” + tableName + ” (” + fieldList + “) values(“);
for (int i = 0; i < values.Length; i++)
{
values[i] = Common.KeyValidate(values[i]);
qry.Append(values[i] + ((i == (values.Length – 1)) ? “) ” : “,”));
}
ExecuteQuery(qry.ToString());
}

public static void UpdateTable(string tableName,string condition, string fieldList, params string[] values)
{
string[] fieldNames = fieldList.Split(‘,’);
if (fieldNames.Length != values.Length)
throw new Exception(“DataAccess : Field names and values not matching…”);

StringBuilder qry = new StringBuilder(“Update ” + tableName + ” Set “);
for (int i = 0; i < values.Length; i++)
{
values[i] = Common.KeyValidate(values[i]);
qry.Append(fieldNames[i] + ” = ” + values[i] + ((i == (values.Length – 1)) ? ” ” : “,”));
}
qry.Append(” where ” + condition);
ExecuteQuery(qry.ToString());
}
#region //Procedure Region

public static void StoreprocedureExecuteQuery(string ProcedureName, string Parameters, params string[] values)
{

OleDbCommand cmd = new OleDbCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(‘,’);
if (fieldNames.Length != values.Length)
throw new Exception(“DataAccess : Field names and values not matching…”);

for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
cmd.ExecuteNonQuery();

}

public static string StoreprocedureExecuteQueryReturned(string ProcedureName, string Parameters, params string[] values)
{

OleDbCommand cmd = new OleDbCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(‘,’);
if (fieldNames.Length != values.Length)
throw new Exception(“DataAccess : Field names and values not matching…”);

for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}

OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string s= reader.GetValue(0).ToString();
reader.Close();
return s;
}
reader.Close();
return “”;

}

public static DataTable QueryStoreprocedure(string ProcedureName)
{
OleDbCommand cmd = new OleDbCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
DataTable DT = new DataTable();
ADPTR = new OleDbDataAdapter(cmd);
ADPTR.Fill(DT);
return DT;
}

public static DataSet QueryStoreprocedureDS(string ProcedureName)
{
OleDbCommand cmd = new OleDbCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
DataSet DS = new DataSet();
ADPTR = new OleDbDataAdapter(cmd);
ADPTR.Fill(DS);
return DS;
}

public static DataTable QueryStoreprocedure(string ProcedureName, string InputParameters, params string[] values)
{
OleDbCommand cmd = new OleDbCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = InputParameters.Split(‘,’);
if (fieldNames.Length != values.Length)
throw new Exception(“DataAccess : Field names and values not matching…”);

for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
DataTable DT = new DataTable();
ADPTR = new OleDbDataAdapter(cmd);
ADPTR.Fill(DT);
return DT;
}

#endregion

#region //Image Region

public static void insertimage(string ImagePath, string StudId,string Mode)
{
byte[] data = null;
string Query = “”;
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
if (Mode == “1”)
{
Query = “Insert into AD_StudentImage values(‘” + StudId + “‘,@ImageData1)”;
}
else if (Mode == “2”)
{
Query = “Insert into AD_StaffImage values(‘” + StudId + “‘,@ImageData1)”;
}
OleDbCommand cmd = new OleDbCommand(Query, CONDB);
cmd.Parameters.Add(new OleDbParameter(“@ImageData1”, (object)data));
cmd.ExecuteNonQuery();

}

public static void updateimage(string ImagePath, string StarName, string Actor, string EditCode)
{
byte[] data = null;
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
string Query = “update Actors set ActorName='” + StarName + “‘ ,Gender='” + Actor + “‘,Image=@ImageData1 where Id=” + EditCode + “”;
OleDbCommand cmd = new OleDbCommand(Query, CONDB);
cmd.Parameters.Add(new OleDbParameter(“@ImageData1″, (object)data));
cmd.ExecuteNonQuery();

}

public static void updateimage(string ImagePath, int StudId,string Mode)
{
byte[] data = null;
string Query=””;
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
if(Mode==”1″)
{
Query = “update AD_StudentImage set StudImage=@ImageData1 where StudId=” + StudId + “”;
}
else if (Mode == “2”)
{
Query = “update AD_StaffImage set StaffImage=@ImageData1 where StaffId=” + StudId + “”;
}
OleDbCommand cmd = new OleDbCommand(Query, CONDB);
cmd.Parameters.Add(new OleDbParameter(“@ImageData1″, (object)data));
cmd.ExecuteNonQuery();
}

#endregion

}

web config

<connectionStrings>
<add name=”DatabaseConnection” connectionString=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|StructureFacadesDataBase.mdb”
providerName=”System.Data.OleDb” />
</connectionStrings>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s