Connecting from C#.Net to SQL Server Like VFP's SQL Pass Through

If one wants to manipulate an SQL Server Database from C#.Net over a local area network in a similar manner that we do in Visual Foxpro through SQL Pass Through, it may not hurt to consider a simple technique that I use to achieve that.

Our friendly neighbourhood Samir Ibrahim from Lebanon has demonstrated to me a different approach using datasets via Ado.Net in a disconnected fashion which is interesting as well. But for this post, I will only demonstrate my approach first and I hope to discuss Samir’s approach in my future posts.

To make things easier and our server name changeable, we will write our connection string to a configuration file that comes along with a C# project.  The following is an example of a connection string added to a configuration file.  The filename is usually the project name plus a suffix.

For example, mine is MyProject.vshost.exe.config which is usually found in \YourProject\bin\Debug folder.

The Configuration File

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="MyConfigString" connectionString="server=MyServer;database=MyDatabase;user id=MyID;password=MyPassword;integrated security=false;connection timeout=30" />

  </connectionStrings>

</configuration>

The Database Connection Class

To make the connection handle available for calling in all forms, I suggest that we create a class that handles and stores that connection. We'll shall name the connection string MyConfigString.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace MyProject

{

    class DatabaseClass

    {

        static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConfigString"].ConnectionString;

        public static SqlConnection myConnection = new SqlConnection(connectionString);   

       

        public DatabaseClass()

        {}

    }

}

Extracting Records and Displaying

In one of your forms, you may want to show records extracted from a table to a specific control, say, a ListView control for example. The following codes below show how it is done.We'll pass along a parameterized SQL query through the SQLCommand. 

Our connection handle in this example will be DatabaseClass.MyConnection. 

SqlParameter Par1 = new SqlParameter("@Param1", SqlDbType.Varchar, 20);

      Par1.Value = "000000121";

SqlDataReader DR = Null;

      String lcQueries = "";

lcQueries = @"Select t1.Field1, t1.Field1, t2.Field3,

From MyDatabase.dbo.MyTable T1

Where T1.Field1 = @Param1";

SqlCommand SqlCommand = new SqlCommand(lcQueries, DatabaseClass.myConnection);

      SqlCommand.Parameters.Add(Par1);

lvMyListView.Items.Clear();

      DR = SqlCommand.ExecuteReader();

      while (DR.Read())

{

ListViewItem List = new ListViewItem(DR["Field1"].ToString());

      list.SubItems.Add(DR["Field2"].ToString());

      list.SubItems.Add(DR["Field3"].ToString());

      lvMyListView.Items.AddRange(new ListViewItem[] { List });

      }

DR.Close();

Basically, what we did was to extract records from an SQL Server database using the command SQLCommand, reiterated through the extracted records and added each record to our list view control.

Hope this helps.