bingaloman bingaloman -4 years ago 134
SQL Question

How does an application connect to a SQL Server database?

So at my workplace we have many applications that we use and a lot of them have their own server for the application itself and a separate server for the database of that application which can be managed in SSMS.

I was just wanting to know, how does an application connect to a SQL Server database? Like for example, when entering in information into the application, how does it get updated and put in the database on a different server than the application itself?

Answer Source

To piggy back off of @Cade Roux's answer, I will try to explain how a programmer would connect and execute SQL queries to the server. I'm going to assume that because you are using SSMS, then your company is full .Net stack. I will use C# as the programming language for the demo. Please bare in mind that this is a high level explanation and is not to be used as best practice.

An application will need two things to execute a SQL query: the connection string, and the SQL command to be executed. The following is sample code:

...(other code)...

string queryS = @"Update myTable Set sqlField = 'datatoupdate' Where otherSqlField = 'whyweareupdatingthisrow'";
string connS = @"Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

CreateCommand(queryS, connS);


private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

'queryS' is the query string. I'm assuming you are familiar with this as you have used SSMS.

'connS' contains the connection string. This is what allows an application to open a connection and execute a SQL query with a database. Within it, you can see that the server and database names are provided, as well as what credentials are needed (same ones used to access the database via SSMS). This allows an application to access a database, even on a different server. Note that this is just a generic connection string and other parameters can be passed into it as well.

The remaining logic calls a function that opens the connection and executes the query.

Additional information on SQL queries using C# can be found here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download