How to connect to remote SQL Server database using Visual C#?

Question

I am creating a Windows forms application and my SQL Server database is on a remote server. How can I connect to it using Visual C# and ADO.NET?

Answer 1

The answer can be found here -

http://stackoverflow.com/questions/402355/connect-to-remote-mysql-database-with-visual-c

Also, read up and download from here - http://dev.mysql.com/downloads/connector/net/5.2.html

Answer 2

You need to investigate the SqlConnection, SqlCommand and possible SqlDataReader and SqlDataAdapter components in .NET (see the MSDN online docs).

Once you have that, you need to define your connection string - check that site link for a huge selection and explanation of connection strings.

Then you basically connect using:

using(SqlConnection conn = new SqlConnection('your connection string here'))
{
    conn.Open();
    // do stuff
    conn.Close();
}

and you can do stuff in various ways, e.g. by filling data sets, reading values etc.

Read the MSDN ADO.NET Overview to get started! Or Google for "ADO.NET tutorial" - you'll find tons of links.

Answer 3

In the eye of MS SQL Server it is no difference where your SLQ Server is located. All you need is to make sure you have access that server in terms of IP and Port number.

Answer 4

Use the below code to create the connection objects necessary.

 public bool BeginTransaction(string strServerName)
    {
        try
        {
            bool bRet = OpenConnection(strServerName);
            if (bRet)
            {
                m_objTransaction = m_conn.BeginTransaction();
                m_dtAdapter.SelectCommand.Connection = m_conn;
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
        return false;
    }
public bool OpenConnection(string strServerName)
    {
        try
        {
            m_connStr = string.Empty;
            m_connStr = string.Format("Data Source=;Initial Catalog=;User Id=sa;Password=;"); //write your credentials here with DB name and server
            m_conn = new SqlConnection(m_connStr);
            m_conn.Open();

            m_dtAdapter = new SqlDataAdapter();

            if (m_conn != null)
            {
                m_dtAdapter.SelectCommand = new SqlCommand();
            }
        }
        catch (SqlException ex)
        {
            return false;
        }
        catch (Exception ex)
        {
            return false;
        }
        return true;
    }