private DataInform InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData, int TierCount, Int64 intCustomerID, int inttype) { int count = 0; DataInform dataInform = new DataInform(); try { connection.Open(); Dictionary <string, string> Tablematch = new Dictionary <string, string>(); using (SqlBulkCopy s = new SqlBulkCopy(connection)) { string strcolumname = string.Empty; foreach (var column in csvFileData.Columns) { strcolumname = strcolumname.Trim() + column.ToString() + " varchar (5000) NULL , "; } foreach (var column in csvFileData.Columns) { s.ColumnMappings.Add(column.ToString(), column.ToString()); } strcolumname = strcolumname.Remove(strcolumname.Length - 2); string strtemptable = "create table MyTempTable999(" + strcolumname + ")"; SqlCommand cmd = new SqlCommand(strtemptable, connection); cmd.CommandTimeout = 180; cmd.ExecuteNonQuery(); s.DestinationTableName = "MyTempTable999"; s.WriteToServer(csvFileData); connection.Close(); dataInform = AddMultipleProducts("MyTempTable999", TierCount, intCustomerID, inttype); } } catch (Exception ex) { log.logErrorMessage(""); log.logErrorMessage(ex.StackTrace); return(dataInform); } finally { connection.Close(); } return(dataInform); }
public List <dynamic> InsertExcelRecords(string path, int Tiercount, Int64 intCustomerID, int inttype) { int count = 0; List <dynamic> objDynamic = new List <dynamic>(); DataInform dataInform = new DataInform(); try { // ExcelConn(_path); //string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Mode=Read;Extended Properties=Excel 12.0;Persist Security Info=False"); //OleDbConnection Econ = new OleDbConnection(constr); //string Query = string.Format("select * from[Sheet1$]"); //OleDbCommand Ecom = new OleDbCommand(Query, Econ); //Econ.Open(); //DataSet ds = new DataSet(); //OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ); //Econ.Close(); //oda.Fill(ds); //DataTable Exceldt = ds.Tables[0]; //foreach (var column in Exceldt.Columns) //{ // Exceldt.Columns[column.ToString()].ColumnName = column.ToString().Replace(" ", string.Empty).Replace("#", string.Empty); // Exceldt.AcceptChanges(); //} DataTable Exceldt = GetDataTabletFromCSVFile(path); dataInform = InsertDataIntoSQLServerUsingSQLBulkCopy(Exceldt, Tiercount, intCustomerID, inttype); objDynamic.Add(dataInform); return(objDynamic); } catch (Exception ex) { log.logErrorMessage(""); log.logErrorMessage(ex.StackTrace); return(objDynamic); } }
private DataInform AddMultipleProducts(string strTableName, int Tiercount, Int64 intCustomerID, int inttype) { int productID = 0; DataInform dataInform = new DataInform(); string insertProcedure = string.Empty; if (inttype == 1) { insertProcedure = "[InsertMulitpeProductData]"; } else if (inttype == 2) { insertProcedure = "[InsertMulitpeCategoryData]"; } SqlCommand insertCommand = new SqlCommand(insertProcedure, connection); insertCommand.CommandType = CommandType.StoredProcedure; insertCommand.CommandTimeout = 180; if (intCustomerID != 0) { insertCommand.Parameters.AddWithValue("@CustID", intCustomerID); // Need to check UI } else { insertCommand.Parameters.AddWithValue("@CustID", 0); } if (!string.IsNullOrEmpty(UserID)) { insertCommand.Parameters.AddWithValue("@UserID", Convert.ToInt64(UserID)); } else { insertCommand.Parameters.AddWithValue("@UserID", 0); } if (Tiercount != 0) { insertCommand.Parameters.AddWithValue("@TierCount", Tiercount); } else { insertCommand.Parameters.AddWithValue("@TierCount", 0); } if (!string.IsNullOrEmpty(strTableName)) { insertCommand.Parameters.AddWithValue("@tablename ", strTableName); } else { insertCommand.Parameters.AddWithValue("@tablename ", DBNull.Value); } insertCommand.Parameters.Add("@ReturnDupValue", System.Data.SqlDbType.Int); insertCommand.Parameters["@ReturnDupValue"].Direction = ParameterDirection.Output; insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int); insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; try { int count = 0; connection.Open(); insertCommand.ExecuteNonQuery(); if (insertCommand.Parameters["@ReturnValue"].Value != DBNull.Value) { dataInform.TotalReccount = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value); } if (insertCommand.Parameters["@ReturnDupValue"].Value != DBNull.Value) { dataInform.TotaProcesscount = System.Convert.ToInt32(insertCommand.Parameters["@ReturnDupValue"].Value); } dataInform.TotalDupcount = dataInform.TotalReccount - dataInform.TotaProcesscount; return(dataInform); } catch (Exception ex) { log.logErrorMessage(""); log.logErrorMessage(ex.StackTrace); return(dataInform); } finally { connection.Close(); } }