none
How to Connect to a SQL database on a different server using c# .net RRS feed

  • Question

  • Hi,

    I have to insert, delete or update data in a SQL 2008 R2 server form within my application(C# .Net). This SQL database and my application will be hosted on different servers. They will be in the same intranet network area. I need to connect to this SQL server from my application and insert the data. 

    The details for the SQL server and my application are given below.

    Database server name -APPSP010

    IP Address - 172.40.60.40

    Database Name - Employee

    Table Name - customer

    UserId - testAccount

    Password- testAccount

    Application will be hosted on an environment with a different IP(172.60.40.60). 

    Could you please tell me how to I connect to this SQL server not on the same machine and insert the data through my application? I had done some R&D and found the connection string as such 

    SqlConnection myConnection = new SqlConnection("user id=username;" + 

                                           "password=password;server=serverurl;" + 

                                           "Trusted_Connection=yes;" + 

                                           "database=database; " + 

                                           "connection timeout=30");

    myConnection.Open();

    {

     // Write Insert, Delete, Update Statements here 

    }

    But this does not specify how do I give the serverURL and the remaining details. Could you please tell me how do I achieve this as I am quite new to this.

    Any help will be greately appreciated.

    Regards,

    Raj

    • Moved by Bob Beauchemin Tuesday, January 31, 2012 5:04 PM Moved to the appropriate forum (From:.NET Framework inside SQL Server)
    Tuesday, January 31, 2012 8:24 AM

Answers

All replies

  • Hi,

    First you have to check your server  allowed to remote access, by using SQL SERVER CONFIGURATION MANAGER

      

     

    Then for insert, delete, update and select I usually prefer using stored procedure, so create your stored procedure containing logic you wan. Check this link.

    Then use this codes:

     

    //Conneciton to the server
    string conn = "Data Source=servername;Initial Catalog=DBNAME;User ID=test;Password=test";
    SqlConnection con;
    con = new SqlConnection(conn);
    con.open();
    

     


    For Selection (Search) use this: change StoreProcedureName with your Procedure and if you have parameter check in the insert and delete how to pass it

     

    SqlCommand cmd = new SqlCommand("StoredProcedureName", con);
     cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    ds = new DataSet();
    da.Fill(ds);
    

    For Insert, Delete and Update use this:

     

     

    SqlCommand cmd = new SqlCommand("StoreProcedureName", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@PID", SqlDbType.BigInt).Value = TextBox1.Text;
    cmd.Parameters.Add("@Name", SqlDbType.NVARCHAR, 50).Value = TextBox2.Text;
    cmd.Parameters.Add("@Phone", SqlDbType.Int).Value = TextBox3.Text;
    cmd.ExecuteNonQuery();
    

    Remember that you have to use try catch for exception handling.

     

     

    I hope this is helpful.

     

    Elmozamil Elamir

    MyBlog

     

     


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com
    Tuesday, January 31, 2012 9:20 AM
  •  

    Raj,

    In the connection string the 'server=serverurl',

    here the serverurl is the name of your server, just change this setting and then you can connect to other server.

    and for more information of the connection string, I will suggest you to refer http://www.connectionstrings.com/sql-server-2008

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Tuesday, January 31, 2012 9:47 AM
  • Hi,

    To connect to the server I gave the following lines of code

     

    SqlConnection myConnection = new SqlConnection("Data Source=APPSP010\\SQLEXPRESS;Initial Catalog=Employee;User Id=testAccount;Password=123456;");

                try

                    {

                        myConnection.Open();

                      Console.WriteLine("Connection Successful");

                     }

                    catch (Exception e)

                     {

                         Console.WriteLine("Exception is    " +e);

                         Console.WriteLine("Connection Not successful");

                     }        

    When I execute the myConnection.Open() line in the above code, it goes to the catch block and gives the following exception.

     “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.”

    I am logged in to my development machine through an account remotely and trying to access the SQL from there by establishing a connection to this SQL Server(which is a different server).

     

    Could you please advice what is going wrong here? The details for the Server Name, Database etc have been entered correctly.

     

    Regards,

    Raj

     

    Wednesday, February 1, 2012 12:22 PM
  • You can Try with IP Name rather than SystemName 172.40.60.40\\SQLEXPRESS

    You can verify troublshooting check list from here

    http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/


    Lingaraj Mishra
    • Marked as answer by Raj190 Monday, February 6, 2012 12:44 PM
    Wednesday, February 1, 2012 12:36 PM
  • Hi,

    I have tried that but it gives the same Exception as above. Does this exception have anything to do with enabling the SQL server to allow remote connections or something?

    Regards,

    Raj

    Wednesday, February 1, 2012 12:52 PM
  • Raj,

    Please follow

    http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Marked as answer by Raj190 Monday, February 6, 2012 12:44 PM
    Wednesday, February 1, 2012 2:47 PM
  • Have a look at server configuration to allow Remote Connection.


    Lingaraj Mishra
    Thursday, February 2, 2012 6:22 AM