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(); } }