How to load datatable into database

[VA_er]

First, please don't move this thread to Excel forum, thanks.

Database information: Sybase Adaptive Server Drive

As newbie, I have really spent a lot of time searching on the web, but have not solved my issue. I am sorry that I keep asking this question, since my issue has not really been resolved from earlier threads.

Now I decide to re-organize my thread, and keep my questions as clearly as possible. (Most of my questions are the comments inside the code).

I really need help from someone who can take a closer look at my code structure, and let me know how to fix my code.

Please don't Copy and Paste a lot of non-related information from other places, those information are typically general information. It does not help solving my question, and still make my thread look like being answered.

Thank you very much for reading this thread.

var filePath = string.Empty;
OpenFileDialog OpenFile = new OpenFileDialog();

OpenFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
OpenFile.Title = "Choose excel file";
OpenFile.FilterIndex = 2; //Don't know what it mean
OpenFile.RestoreDirectory = true;

if (OpenFile.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = OpenFile.FileName;
}

//Read excel data into DataTable

Excel.Application xlApp = new Excel.Application();

Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
//The excel file has only one sheet, with header, want to import Column A-G into database
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = 7; //Copy 7 columns only, in my case

DataTable table = new DataTable();



for (int i = 1; i <= colCount; i++)
{

if ((string)(xlWorksheet.Cells[1, i]) == "Field1")
CField1 = i; //Find out which column is Field1 in Excel file, since Excel columns could be in different order(Field1 may not be in Column A).
else if ((string)(xlWorksheet.Cells[1, i]) == "Field2")
CField2 = i;
else if ((string)(xlWorksheet.Cells[1, i]) == "Field3")
CField3 = i;
else if ((string)(xlWorksheet.Cells[1, i]) == "Field4")
CField4 = i;
else if ((string)(xlWorksheet.Cells[1, i]) == "Field5")
CField5 = i;
else if ((string)(xlWorksheet.Cells[1, i]) == "Field6")
CField6 = i;
else if ((string)(xlWorksheet.Cells[1, i]) == "Field7")
CField7 = i;


}

table.Columns.Add("Field1", typeof(int));
table.Columns.Add("Field2", typeof(int));
table.Columns.Add("Field3", typeof(int));
table.Columns.Add("Field4", typeof(string));
table.Columns.Add("Field5", typeof(int));
table.Columns.Add("Field6", typeof(string));
table.Columns.Add("Field7", typeof(float));

for (int i = 2; i <= rowCount; i++)
{
table.Rows.Add(xlRange.Cells[i, CField1].Value, xlRange.Cells[i, CField2].Value, xlRange.Cells[i, CField3].Value, xlRange.Cells[i, CField4].Value, xlRange.Cells[i, CField5].Value, xlRange.Cells[i, CField6].Value, xlRange.Cells[i, CField7].Value);
}

OdbcConnection Cn = new OdbcConnection("ConnectionString");
Cn.Open();

//Database table name is XYZ, how to know which record is already in the database.
for (int i = 1; i <= table.Rows.Count; i++)
{
int Value1 = Convert.ToInt32(table.Rows[CField1]);
int Value2 = Convert.ToInt32(table.Rows[CField2]);
int Value3 = Convert.ToInt32(table.Rows[CField3]);
string Value4 = table.Rows[CField4];
int Value5 = Convert.ToInt32(table.Rows[CField5]);
string Value6 = table.Rows[CField6];
float Value7 = ???; //How to write float?

string Query = String.Format("SELECT COUNT(*) from XYZ where Field1 = '{0}' AND Field2 = '{1}' AND Field3 = '{2}' AND Field4 = '{3}' AND Field5 = '{4}' AND Field6 = '{5}'", Value1, Value2, Value3, Value4, Value5, Value6);
//When the first SIX fields are same, then the record is determined to be DUPLICATE. I guess in this case, just use Update the database record, regardless if Field7 is same or not.
//If not all SIX fields are same, the it is determined to be DIFFERENT record.
//Probably need Try Catch, in case Covert.Into32 gets involved with some non numeric input.


}

//Every time going through an element in the above FOR loop, it will run a query. Is there a way to push whole datatable into database, and let the syetem to determine if there is error message, that being said, use Try Catch statement. If that is possible, I don't really need to check each row in datatable, just need to match datatable column name against database table field name.






Cn.Close();











Continue reading...