public bool CheckViewExists()
{
DataSet ds = new DataSet();
bool ret = false;
try
{
DataTable dt = ExecuteQuery("SELECT * FROM VW_CARD_COUNTS;").Tables[0].Copy();
dt.TableName = "CARD_COUNTS";
ds.Tables.Add(dt);
ret = true;
}
catch (Exception ex)
{
ret = false;
BL_Error_Log.WriteLog(ex);
}
return(ret);
}
public bool Insert_data(OleDbConnection Connection, DataSet ds)
{
//Ds Tables Mapping
//Table0= Table1
//Table1=Tbl_PLMaster
//Table2=Tbl_ACMaster
//Table3=Tbl_PSMaster
//Table4=Tbl_VotersMaster
//Table5=Tbl_PollingDetails
//Table6=Tbl_PollingOtherDetails
//Table7=PlusMinus
//Table8=Plusminusdetails
try
{
Connection.Open();
OleDbCommand cmd;
string qry;
foreach (DataTable dt in ds.Tables)
{
switch (dt.DataSet.Tables.IndexOf(dt))
{
case 1:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_PLMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "'" + dr[dc].ToString() + "',";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
new OleDbCommand(qry, Connection).ExecuteNonQuery();
}
#endregion
break;
case 2:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_ACMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 3:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_PSMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 4:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_VotersMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString().Trim().Length <= 0 ? "0" : dr[dc].ToString());
}
try { cmd.ExecuteNonQuery(); }
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 5:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_PollingDetails values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 6:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_PollingOtherDetails values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 7:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into PlusMinus values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 8:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Plusminusdetails values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 9:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_GroupMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 10:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_UserMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
}
}
Connection.Close();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Export Data", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return(false);
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
public bool Insert_data(OleDbConnection Connection, DataSet ds)
{
//Ds Tables Mapping
//0 Tbl_AreaMaster
//1 Tbl_BankMaster
//2 Tbl_CardholderMaster
//3 Tbl_CardholderwiseMember
//4 Tbl_CardTypeMaster
//5 Tbl_DistrictMaster
//6 Tbl_EmployeeMaster
//7 Tbl_GroupMaster
//8 Tbl_ShopCategoryMaster
//9 Tbl_ShopMaster
//10 Tbl_StateMaster
//11 Tbl_TalukaMaster
//12 Tbl_UserMaster
//13 Tbl_VillageMaster
OleDbCommand cmd = new OleDbCommand();
try
{
Connection.Open();
string qry;
foreach (DataTable dt in ds.Tables)
{
switch (dt.DataSet.Tables.IndexOf(dt))
{
case 0:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_AreaMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 1:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_BankMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
break;
case 2:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_CardholderMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 3:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_CardholderwiseMember values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 4:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_CardTypeMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString().Trim().Length <= 0 ? "0" : dr[dc].ToString());
}
try { cmd.ExecuteNonQuery(); }
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 5:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_DistrictMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 6:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_EmployeeMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 7:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_GroupMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 8:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_ShopCategoryMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 9:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_ShopMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 10:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_StateMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 11:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_TalukaMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 12:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_UserMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
case 13:
#region
foreach (DataRow dr in dt.Rows)
{
qry = "insert into Tbl_VillageMaster values(";
foreach (DataColumn dc in dt.Columns)
{
qry += "@" + dc.ColumnName.ToString() + ",";
}
qry = (qry.Trim().Remove(qry.Length - 1)) + ");";
cmd = new OleDbCommand(qry, Connection);
foreach (DataColumn dc in dt.Columns)
{
cmd.Parameters.AddWithValue("@" + dc.ColumnName.ToString(), dr[dc].ToString());
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex) { BL_Error_Log.WriteLog(ex); }
}
#endregion
break;
}
}
Connection.Close();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Export Data", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return(false);
}
finally
{
Connection.Close();
Connection.Dispose();
cmd.Dispose();
}
}
