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