Hi
In My Project, I'm Import data from Excel to Sql server 2008 Using C# Winforms 2010.
I can read the Excel File by Mapping the Columns in First row of the Column Header and store it to sql DataBase Table . But the Problem is That Suppose in Excel File First row is Been Changed to Second Row or AnyOther Rows Then C# Code Can't Map the Rows and Through an Error. So How to Read specific columns in excel file and store in sql table using c# windows application ?
Here Is My Code .
Can Any One Help Me With Right Answer
private void BtnSubmit_Click(object sender, EventArgs e)
{
if (IcCutomerName.Text != "" && CustSalesDate.Text != "")
{
ImportExcel();
PopulateListView2();
AutoCompleteSalesNo();
AllClear();
}
else
{
if (MessageBox.Show("Need Customer Name To Submit", "Message", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
IcCutomerName.Focus();
}
else
{
BtnSubmit.Focus();
}
}
}
public void ImportExcel()
{
try
{
try { Connection(); }
catch { return; }
cmd = new SqlCommand("Delete from ExcelCustomerSales", cs);
cmd.ExecuteNonQuery();
cs.Close();
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TbFilePath.Text + "; Extended Properties=\"Excel 12.0;HDR=Yes\"";
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
try { Connection(); }
catch { return; }
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cs))
{
bulkCopy.DestinationTableName = "ExcelCustomerSales";
bulkCopy.ColumnMappings.Add("Item/Model Code", "ModelCode");
bulkCopy.ColumnMappings.Add("Item/Model", "ModelName");
bulkCopy.ColumnMappings.Add("Qty", "Quantity");
bulkCopy.ColumnMappings.Add("Sales Value", "SalesValue");
bulkCopy.ColumnMappings.Add("Discount Amt", "DiscountAmt");
bulkCopy.ColumnMappings.Add("Amount", "Amount");
bulkCopy.ColumnMappings.Add("Net Amount", "NetAmount");
bulkCopy.ColumnMappings.Add("SR Qty", "SRQty");
bulkCopy.ColumnMappings.Add("SR Value", "SRValue");
bulkCopy.ColumnMappings.Add("SR Discount Amt", "SRDiscountAmt");
bulkCopy.ColumnMappings.Add("SR Tax Amt", "SRTaxAmt");
bulkCopy.ColumnMappings.Add("SR Net Amt", "SRNetAmt");
bulkCopy.ColumnMappings.Add("Total Qty", "TotalQty");
bulkCopy.ColumnMappings.Add("Total Value", "TotalValue");
bulkCopy.ColumnMappings.Add("Total Discount", "TotalDiscount");
bulkCopy.ColumnMappings.Add("Total Tax", "TotalTax");
bulkCopy.ColumnMappings.Add("Total Net Value", "TotalNetValue");
bulkCopy.WriteToServer(dr);
}
}
connection.Close();
cs.Close();
EmptyData();
ErrorAlert();
MessageBox.Show("Data Exported To Sql Server Successfully");
}
}
catch
{
MessageBox.Show("Data Exported To Sql Server has been Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void EmptyData()
{
try { Connection(); }
catch { return; }
cmd = new SqlCommand("Delete from ExcelCustomerSales Where ModelCode IS NULL AND ModelName IS NULL AND Quantity IS NULL AND SalesValue IS NULL AND DiscountAmt IS NULL AND Amount IS NULL AND NetAmount IS NULL AND SRQty IS NULL AND SRValue IS NULL AND SRDiscountAmt IS NULL AND SRTaxAmt IS NULL AND SRNetAmt IS NULL AND TotalQty IS NULL AND TotalValue IS NULL AND TotalDiscount IS NULL AND TotalTax IS NULL AND TotalNetValue IS NULL", cs);
cmd.ExecuteNonQuery();
cs.Close();
}
public void ErrorAlert()
{
try { Connection(); }
catch { return; }
int Num1 = 0, Num2 = 0;
cmd = new SqlCommand("Select MAX(ID) from CustomerSalesNo", cs);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Num1 = int.Parse(dr[0].ToString());
}
dr.Close();
Num2 = (Num1 + 1);
cmd = new SqlCommand("Insert Into CustomerSalesNo(OsDcNo) Values(GetDate())", cs);
cmd.ExecuteNonQuery();
getDate = SqlDateTime.Null;
DateTime? ads = null;
try
{
ads = Convert.ToDateTime(CustSalesDate.EditValue);
}
catch
{
}
string CustomerCode ="", CategoryName="", MMCode = "", ModelCode="", ModelName = "", Quantity = "", ColorName="", ModelSize="", SalesValue = "", DiscountAmt = "", Amount = "", NetAmount = "", SRQty = "", SRValue = "", SRDiscountAmt = "", SRTaxAmt = "", SRNetAmt = "", TotalQty = "", TotalValue = "", TotalDiscount = "", TotalTax = "", TotalNetValue = "", CMID = "", SMID = "", CUSTID = "", MMID = "", Remarks = "Customer Sales Report Saved";
string CustomerName = IcCutomerName.Text;
string SalesNo = Num2.ToString() + "-" + CustSalesDate.EditValue;
SqlDataAdapter da = new SqlDataAdapter("Select ModelCode, ModelName, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue from ExcelCustomerSales", cs);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow Row in dt.Rows)
{
ModelCode = Row["ModelCode"].ToString();
ModelName = Row["ModelName"].ToString();
Quantity = Row["Quantity"].ToString();
SalesValue = Row["SalesValue"].ToString();
DiscountAmt = Row["DiscountAmt"].ToString();
Amount = Row["Amount"].ToString();
NetAmount = Row["NetAmount"].ToString();
SRQty = Row["SRQty"].ToString();
SRValue = Row["SRValue"].ToString();
SRDiscountAmt = Row["SRDiscountAmt"].ToString();
SRTaxAmt = Row["SRTaxAmt"].ToString();
SRNetAmt = Row["SRNetAmt"].ToString();
TotalQty = Row["TotalQty"].ToString();
TotalValue = Row["TotalValue"].ToString();
TotalDiscount = Row["TotalDiscount"].ToString();
TotalTax = Row["TotalTax"].ToString();
TotalNetValue = Row["TotalNetValue"].ToString();
SqlDataAdapter da1 = new SqlDataAdapter("Select ModelCode, CategoryName, ModelSize, ID, MSID from ModelMaster where ModelCode='" + ModelCode + "'", cs);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
foreach (DataRow Row1 in dt1.Rows)
{
MMCode = Row1["ModelCode"].ToString();
CategoryName = Row1["CategoryName"].ToString();
ModelSize = Row1["ModelSize"].ToString();
MMID = Row1["ID"].ToString();
SMID = Row1["MSID"].ToString();
}
SqlDataAdapter da2 = new SqlDataAdapter("Select CustomerCode, ID from CustomerMaster where CustomerName='" + IcCutomerName.Text + "'", cs);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
foreach (DataRow Row2 in dt2.Rows)
{
CustomerCode = Row2["CustomerCode"].ToString();
CUSTID = Row2["ID"].ToString();
}
if (ModelCode.ToUpper() == MMCode.ToUpper())
{
try
{
cmd = new SqlCommand("Insert Into CustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
cmd.ExecuteNonQuery();
}
catch { }
}
else
{
try
{
cmd = new SqlCommand("Insert Into ErrorCustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
cmd.ExecuteNonQuery();
}
catch { }
}
}
cs.Close();
}