Saturday, August 24, 2013

Using SqlBulkCopy to perform bulk copy operations from one database to other database/SqlBulkCopy Class in C#.

 SqlBulkCopy is a class that was added to the .NET Framework version 2.0. It provides feature likeimport data from one database to other Microsoft SQL Server database.By using this you can get data from one database manupulate data then bulk copy that data into other database programmatically.  
 public int ImportData(string connectionString)  
     {   
       SqlConnection connection = null;  
       SqlCommand cmd = null;  
       string fname = string.Empty;  
       string lname = string.Empty;  
       string addr1 = string.Empty;  
       string addr2 = string.Empty;  
       string city = string.Empty;  
       string state = string.Empty;  
       string zip = string.Empty;  
       try  
       {  
         DataTable myDataTable = GetCreatedTable();           
         connection = new SqlConnection(connectionString);  
         DataSet Ds = new DataSet();          
         connection.Open();  
         string sqlcmd = "SELECT * from tablename";  
         cmd = new SqlCommand(sqlcmd, connection);  
         cmd.CommandType = CommandType.Text;  
         SqlDataAdapter da = new SqlDataAdapter(cmd);  
         da.Fill(Ds);  
         if (Ds.Tables[0].Rows.Count > 0)  
         {  
           foreach (DataRow dr in Ds.Tables[0].Rows)  
           {  
             DataRow drData = myDataTable.NewRow();  
             fname = Convert.ToString(dr["fname"]);  
             lname = Convert.ToString(dr["lname"]);  
             addr1 = Convert.ToString(dr["addr1"]);  
             addr2 = Convert.ToString(dr["addr2"]);  
             city = Convert.ToString(dr["city"]);  
             state = Convert.ToString(dr["state"]);  
             zip = Convert.ToString(dr["zip"]);  
             drData["FirstName"] = fname;  
             drData["LastName"] = lname;  
             drData["Add1"] = addr1;  
             drData["Addr2"] = addr2;  
             drData["City"] = city;  
             drData["State"] = state;  
             drData["Zip"] = zip;  
             myDataTable.Rows.Add(drData);            
           }  
           string connString = ConfigurationManager.AppSettings["DestConnString"].ToString();  
           using (SqlConnection dbConnection = new SqlConnection(connString))  
           {  
             dbConnection.Open();             
             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnection))  
             {  
               bulkCopy.ColumnMappings.Add("FirstName", "FirstName");  
               bulkCopy.ColumnMappings.Add("LastName", "LastName");  
               bulkCopy.ColumnMappings.Add("Add1", "Add1");  
               bulkCopy.ColumnMappings.Add("Addr2", "Addr2");  
               bulkCopy.ColumnMappings.Add("City", "City");  
               bulkCopy.ColumnMappings.Add("State", "State");  
               bulkCopy.ColumnMappings.Add("Zip", "Zip");  
               bulkCopy.BatchSize = 100;  
               bulkCopy.BulkCopyTimeout = 100;  
               bulkCopy.DestinationTableName = "EmpTable";  
               bulkCopy.WriteToServer(myDataTable.CreateDataReader());                
             }  
           }  
         }  
         else  
         {  
                       //show message for no record found  
           "No record found to transfer data in empdata Database.";  
         }  
       }        
       catch (Exception ex)  
       {  
         throw ex;  
       }  
       finally  
       {  
         connection.Close();  
       }        
       return 1;  
     }  
 public DataTable GetCreatedTable()  
     {  
       DataTable empData = new DataTable("EMPData");  
       DataColumn dcFirstName = new DataColumn();  
       dcFirstName.DataType = Type.GetType("System.String");  
       dcFirstName.ColumnName = "FirstName";  
       DataColumn dcLastName = new DataColumn();  
       dcLastName.DataType = Type.GetType("System.String");  
       dcLastName.ColumnName = "LastName";  
       DataColumn dcAdd1= new DataColumn();  
       dcAdd1.DataType = Type.GetType("System.String");  
       dcAdd1.ColumnName = "Add1";  
       DataColumn dcAdd2 = new DataColumn();  
       dcAdd2.DataType = Type.GetType("System.String");  
       dcAdd2.ColumnName = "Add2";  
       DataColumn dcCity = new DataColumn();  
       dcCity.DataType = Type.GetType("System.String");  
       dcCity.ColumnName = "City";  
       DataColumn dcState = new DataColumn();  
       dcState.DataType = Type.GetType("System.String");  
       dcState.ColumnName = "State";  
       DataColumn dcZip = new DataColumn();  
       dcZip.DataType = Type.GetType("System.String");  
       dcZip.ColumnName = "Zip";  
       empData.Columns.Add(dcFirstName);  
       empData.Columns.Add(dcLastName);  
       empData.Columns.Add(dcAdd1);  
       empData.Columns.Add(dcAdd2);  
       empData.Columns.Add(dcCity);  
       empData.Columns.Add(dcState);  
       empData.Columns.Add(dcZip);  
       return empData;  
     }  

Wednesday, August 14, 2013

Add new record in database usinbg Entity Framework/Entity Framework - Inserting Data to Database

 For adding new objects to the Entity Set, you need to create an instance of an Entity type and add the object to the object context.   
 public bool StudentDetail(StudentDetail objstudetail)  
 {  
       StudentDetail studetail = new StudentDetail();  
       studetail.ID = objstudetail.ID;  
       studetail.Name = objstudetail.Name;  
       studetail.Age = objstudetail.Age;  
       studetail.Address = objstudetail.Address;  
       studetail.City = objstudetail.City;  
       try  
       {     
           using (DBEntities context = new DBEntities())  
           {  
                context.studetails.AddObject(studetail);  
                context.SaveChanges();  
           }  
       }  
       catch (Exception ex)  
       {  
        throw ex;  
       }  
       return true;  
  }  

How to Create connection string dynamically asp.net/c#


 Below code explains how can we create connection string dynamically in web.config file.  
 make a connection string in web.config as given below:  
 <add key="conString" value="Data Source={0};Initial Catalog={1};User ID=sa;Password=pass;Trusted_Connection=False;Persist Security Info=True" />  
 C# code  
 private static string ConnectionString(string dbservername,string dbname)  
 {        
      string connectionString = string.Empty;  
      if (!string.IsNullOrEmpty(dbservername))  
      {  
           connectionString = ConfigurationManager.AppSettings["conString"].ToString();  
           connectionString = string.Format(connectionString, dbservername, dbname);  
      }  
      else  
      {  
           connectionString = "make default connection string";  
      }        
      return connectionString;  
 }  

Sunday, August 11, 2013

This row already belongs to this table asp.net and c#.


Solution : Please check below line of code.Have you implemented in your logic or not? DataRow dreMyData = eMyNewDataTable.NewRow();

The SelectCommand property has not been initialized before calling 'Fill'.


Solutions : You have to specify select command of SqlDataAdapter before filling your table. Your SqlCommand object is not connected in any way to your SqlDataAdapter.Please check below line have you implemented in your code. adp.SelectCommand=cmd;

Losing file in a file upload control in update panel c# asp.net/File Upload Control is not working in Update Panel


 Solution : By Using Trigger option we need to Trigger the upload button from AsyncPostBackTrigger to PostBackTrigger.  
 So that after Postback when user clicked a button to upload file control will available.  
 <asp:ScriptManager ID="ScriptManager1" runat="server" />  
 <asp:UpdatePanel ID="UpdatePanel1" runat="server">  
   <ContentTemplate>  
     <asp:FileUpload ID="File1" runat="server" />  
     <asp:Button ID="btn1Upload" runat="server"  
       Text="Async_Upload" OnClick = "Upload_File" />  
     <asp:Button ID="btnUpload" runat="server" Text="Upload"  
       OnClick = "Upload_File" />          
   </ContentTemplate>  
   <Triggers>  
     <asp:AsyncPostBackTrigger ControlID = "btn1Upload"  
      EventName = "Click" />  
     <asp:PostBackTrigger ControlID = "btnUpload" />  
   </Triggers>  
 </asp:UpdatePanel>  

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.


 Solution : Please check below code which is working in my case.After solving above error.  
 public IList<EMPData> GetEMPDataList(Guid empid)  
     {  
       IList<EMPData> objEMPData = null;  
       try  
       {  
         using (EMPDataDBEntities context = new EMPDataDBEntities3())  
         {  
           objData = context.EMPDatas.Where(entity => entity.ID == 0 && entity.EMPCode == (Guid)empid).ToList();  
         }  
       }  
       catch (Exception ex)  
       {  
         m_log.trace(ex);  
       }  
       return objEMPData ;  
     }  

Could not install : Newtonsoft.Json


Solution : If you failed to install form Manage Newget Package (Right click on project) start this PM> Install-Package Newtonsoft.Json (Tools -> NewGet Package Manager - > Package Manager Console)

Could not load file or assembly 'Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'


Solution : In package manager Console execute: Update-Package –reinstall Newtonsoft.Json.

Access to the path is denied


Solution : 1.check your path is correct 2.permission should be acces to all

Cannot convert lambda expression to type 'string' because it is not a delegate type


Solution : using System.Linq;

System.Net.Http.HttpContent' does not contain a definition for 'ReadAsAsync' and no extension method.


Solution: Add a reference to System.Net.Http.Formatting.dll. This assembly is also available in the C:\Program Files\Microsoft ASP.NET\ASP.NET MVC 4\Assemblies folder.

Unable to start debugging on the web server.The web server is not configured correctly.See help for common configuration errors. Running the web page outside of the debugger may provide further information.


Solution : Open the command prompt and run ASPNET_REGIIS.EXE with -i parameter C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -i

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Company'.


Sol: Check the GridView columns and select query which you are using for bind GridView.Columns should be same in both place.

A network-related or instance-specific error occurred while establishing a connection to SQL Server


Things to check: 1. Make sure your database engine is configured to accept remote connections Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration Click on Surface Area Configuration for Services and Connections Select the instance that is having a problem > Database Engine > Remote Connections Enable local and remote connections Restart instance 2. Check the SQL Server service account If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding. 3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application Usually the format needed to specify the database server is machinename\instancename Check your connection string as well. Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30? providerName=System.Data.SqlClient/> 4.You may need to create an exception on the firewall for the SQL Server instance and port you are using Start > Run > Firewall.cpl Click on exceptions tab Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433) Check your connection string as well. 5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings. 6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser. 7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver Start > Run > cmd netstat -ano| findstr 1433 telnet myserver 1433 ping -a myserver

EXECUTE permission denied on object 'usp_login', database 'Database', schema 'dbo'


Please check your database user roles. It's necessary the database user to be owner. What you should instead do, is grant the user the following Roles: - aspnet_Membership_FullAccess - aspnet_Roles_FullAccess - etc. depending on what features you have enabled/installed into this database Do this by using Microsoft SQL Server Management Studio or Microsoft SQL Server Management Studio Express 1. Connect to the database you are working with 2. Open Databases 3. Open the database you are working with 4. Open Security 5. Open Users 6. Double-click the user who is accessing the database 7. In the list of Owned Schemas, check off the roles indicated above

Parameter count does not match Parameter Value count.


Sol: Check parameter both .net and sp

No overload for method 'GetInstitute' takes '3' arguments


Sol: check your calling mathod parameter not match.

Procedure or Function 'genInstituteMaster_SelectDelete' expects parameter '@Select', which was not supplied.


Sol: Parameter mismatch check in c# code passing parameter.