Hi,
I want to use bulk import in module zero to copy bulk data's from excel sheet. I am using Oledb connection for bulk import. but the connection string is not properly worked. if i use any type of new connection in module zero it shows some error only.
my coding is
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString)) { conn.Open(); DataTable dtExcelSchema = conn.GetSchema("Tables"); string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); string query = "SELECT * FROM [" + sheetName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn); adapter.Fill(ds, "Items"); string con = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; }
i am getting an error in the bold line.how can i solve this problem. how can i use oledb connection in module Zero. help me to resolve this.
Thanks
5 Answer(s)
-
0
Hi,
Can you share the exact error and details. Also, where is that code is located? In a repository or an application service? You can also try to disable transaction (<a class="postlink" href="http://www.aspnetboilerplate.com/Pages/Documents/Unit-Of-Work#DocUowNoTransaction">http://www.aspnetboilerplate.com/Pages/ ... ransaction</a>)
-
0
Hi,
I am defined the coding in Controller. Also i initialized the new OLEDB connection for perform bulk import. If i using a new type connection in module zero it shows the error message of "The transaction has aborted". i need a explanation of how to use oledb connection in module zero. here i attached my full coding also.kindly give a proper guidance to solve this.
Coding:
if (Request.Files.Count <= 0 || Request.Files[0] == null) { //throw new UserFriendlyException(L("ProfilePicture_Change_Error")); } var file = Request.Files[0]; if (file == null) { } if (file.ContentLength > 0) { string filePath = Path.Combine(HttpContext.Server.MapPath("~/Files/LeadFiles/"), Path.GetFileName(file.FileName)); //CreateFileImportDto cc = new CreateFileImportDto(); //cc.FileName = file.FileName; //cc.ReferalSourceId = 1; //cc.ImportDate = DateTime.Now(); //cc. //var rd = db.sp_DMLFileImports(0, file.FileName, fileImport.ReferralSourceId, DateTime.Now, User.Identity.GetUserId(), 0, 0, "Leads", "Pending"); //var fd = rd.ToList(); //string cv = ""; //foreach (var d in fd) //{ // cv = d.ToString(); //} var maxId = (_FileImportRespository.GetAll().Select(x => (int?)x.Id).Max() ?? 0) + 1; var cv = "Yes"; if (cv == "Yes") { long id = 0; filePath = Path.GetDirectoryName(filePath) + "\\"; string FileName = maxId.ToString() + ".xlsx"; filePath = filePath + FileName; file.SaveAs(filePath); DataSet ds = new DataSet(); string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString)) { conn.Open(); DataTable dtExcelSchema = conn.GetSchema("Tables"); string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); string query = "SELECT * FROM [" + sheetName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn); adapter.Fill(ds, "Items"); string con = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { if (ModelState.IsValid) { //long ImportId = (long)fileImport.ReferralSourceId; //string Userid = User.Identity.GetUserId(); //var res = db.sp_DMLFileImports(id, file.FileName, ImportId, DateTime.Now, Userid, ds.Tables[0].Rows.Count, 0, "Leads", "Pending"); //var rs = res.ToList(); //string result = ""; //foreach (var r in rs) //{ // result = r.ToString(); //} var result = "Yes"; if (result == "Yes") { ds.Tables[0].Columns.Add("ImportId", typeof(int)); ds.Tables[0].Columns.Add("RecordStatus", typeof(string)); foreach (DataRow item in ds.Tables[0].Rows) { //item["ImportId"] = id; item["RecordStatus"] = "Pending"; } string[] arryayColumnNames = { "ImportId", "RefNo", "ProjectName", "Country", "Client", "Consultant", "Contractor", "Value", "ProjectType", "Status", "City", "Location", "ConstructionStart", "ConstructionCompletion", "RecordStatus" }; string[] dataColumnsExcel = ds.Tables[0].Columns.Cast<DataColumn>().Select(r => r.ColumnName).ToArray(); foreach (var item in arryayColumnNames) { if (!dataColumnsExcel.Contains(item)) { ViewBag.Error = "Excel ColumnName difference " + item; //var filemport = db.FileImports.Find(id); //db.FileImports.Remove(filemport); //ViewBag.ReferralSourceId = new SelectList(db.ReferralSources, "Id", "ReferralSource1", fileImport.ReferralSourceId); //return View(); } } using (var bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity)) { bulkCopy.DestinationTableName = "dbo.LeadImports"; bulkCopy.ColumnMappings.Clear(); bulkCopy.ColumnMappings.Add("ImportId", "ImportId"); bulkCopy.ColumnMappings.Add("RefNo", "RefNo"); bulkCopy.ColumnMappings.Add("ProjectName", "ProjectName"); bulkCopy.ColumnMappings.Add("Country", "Country"); bulkCopy.ColumnMappings.Add("Client", "Client"); bulkCopy.ColumnMappings.Add("Consultant", "Consultant"); bulkCopy.ColumnMappings.Add("Contractor", "Contractor"); bulkCopy.ColumnMappings.Add("Value", "Value"); bulkCopy.ColumnMappings.Add("ProjectType", "ProjectType"); bulkCopy.ColumnMappings.Add("Status", "Status"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Location", "Location"); bulkCopy.ColumnMappings.Add("ConstructionStart", "ConstructionStart"); bulkCopy.ColumnMappings.Add("ConstructionCompletion", "ConstructionCompletion"); bulkCopy.ColumnMappings.Add("RecordStatus", "RecordStatus"); bulkCopy.WriteToServer(ds.Tables[0]); bulkCopy.Close(); } } } } } } } } //Update new picture on the user //user.ProfilePictureId = storedFile.Id; }I am expecting your reply ASAP.
Thank You
-
0
Hi,
I think this is ProfilerController.ChangeProfilePicture method. Can you try to remove [UnitOfWork] attribute from the method.
-
0
Hi,
It's Perfectly Worked.
Thank you
-
0
UnitOfWork begins transaction and your problem was about transactions. This is why we removed it.
