public string CreateTable(DataTable dtbl, string strWellName) { string strTabName = ""; string strFinal = string.Empty; try { if (dtbl.Columns.Count > 0) { strTabName = "Las_" + strWellName; string strDropQuery = "IF OBJECT_ID('dbo.Las_" + strWellName + "', 'U') IS NOT NULL DROP TABLE dbo.Las_" + strWellName + "; "; string strQuery = strDropQuery + " CREATE TABLE Las_" + strWellName + "("; for (int i = 0; i < dtbl.Columns.Count; i++) { strQuery += "[" + dtbl.Columns[i].ColumnName.ToString() + "] NVARCHAR(MAX),"; } strFinal = strQuery.TrimEnd(',') + ");"; SPMasterSettings objSp = new SPMasterSettings(); objSp.ExicuteRuntimeQuery(strFinal); } } catch (Exception ex) { throw; } return(strTabName); }
private void btnSave_Click(object sender, EventArgs e) { try { if (cmbXValue.SelectedIndex > -1 && cmbYValue.SelectedIndex > -1 && cmbZValue.SelectedIndex > -1) { if (DgvMasterSettings.Rows.Count > 0) { // Truncate Old Data SPMasterSettings objSpTrun = new SPMasterSettings(); objSpTrun.ExicuteRuntimeQuery("TRUNCATE TABLE Tbl_Wells_Details"); // Insert New Data string strXVal = cmbXValue.Text; string strYVal = cmbYValue.Text; string strZVal = cmbZValue.Text; for (int i = 0; i < DgvMasterSettings.Rows.Count; i++) { WellModel objModel = new WellModel(); WellSP objSp = new WellSP(); try { if (DgvMasterSettings.Rows[i].Cells[0].Value != null) { objModel.WellName = DgvMasterSettings.Rows[i].Cells[0].Value.ToString(); objModel.XValue = Convert.ToDecimal(DgvMasterSettings.Rows[i].Cells[strXVal].Value.ToString() != string.Empty ? DgvMasterSettings.Rows[i].Cells[strXVal].Value : 0); objModel.YValue = Convert.ToDecimal(DgvMasterSettings.Rows[i].Cells[strYVal].Value.ToString() != string.Empty ? DgvMasterSettings.Rows[i].Cells[strYVal].Value : 0); objModel.ZValue = Convert.ToDecimal(DgvMasterSettings.Rows[i].Cells[strZVal].Value.ToString() != string.Empty ? DgvMasterSettings.Rows[i].Cells[strZVal].Value : 0); objSp.WellsInsertUpdate(objModel); } } catch (Exception exe) { MessageBox.Show("Well Details Insert Error at: " + objModel.WellName, "EPMS", MessageBoxButtons.OK, MessageBoxIcon.Error); } } MessageBox.Show("All Data Saved to database Successfully..!", "EPMS", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Load the data first", "EPMS", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("X Y Z Values mandatory. Please select it first", "EPMS", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message, "EPMS", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public bool StartCopy(DataTable dtblData, string strTableName) { bool isSuccess = false; try { SPMasterSettings objSp = new SPMasterSettings(); objSp.ExicuteRuntimeQuery("TRUNCATE TABLE " + strTableName); if (dtblData.Rows.Count > 0) { sqlconn = ConfigurationManager.AppSettings["SqlConnection"].ToString(); con = new SqlConnection(sqlconn); //creating object of SqlBulkCopy SqlBulkCopy objbulkRaw = new SqlBulkCopy(con); //assigning Destination table name objbulkRaw.DestinationTableName = "dbo." + strTableName; //Mapping Table column for (int i = 0; i < dtblData.Columns.Count; i++) { objbulkRaw.ColumnMappings.Add(dtblData.Columns[i].ColumnName, dtblData.Columns[i].ColumnName); } //inserting Datatable Records to DataBase con.Open(); objbulkRaw.BulkCopyTimeout = 6000; objbulkRaw.BatchSize = 5000; objbulkRaw.BulkCopyTimeout = 60; objbulkRaw.WriteToServer(dtblData); con.Close(); isSuccess = true; } if (dtblData.Rows.Count == 0) { isSuccess = true; } } catch (Exception ex) { return(false); } return(isSuccess); }