Random Thoughts and Hints on Software Development

From the mail: how can I access database from C#

_______________________________________
From: sandusergiu1984@....com [mailto:sandusergiu1984@....com]
Sent: Fri 8/11/2006 9:20 AM
To: Eldar Musayev
Subject: (Random Thoughts and Hints on Software Development) : C# Code
I thought how can I write a program in C# that searches a SQL Server database and outputs the results to the user? and couldn't give me please some ideas, or a fragment of code? Thanks a lot.
----------------------------------
This message was generated from a contact form at: http://blogs.msdn.com/eldar/default.aspx

Here it is. First, file Class1.cs (that’s a test driver):

using System;

namespace GetData
{
 /// <summary>
 /// Summary description for Class1.
 /// </summary>
 class Class1
 {
  /// <summary>
  /// The main entry point for the application.
  /// </summary>
  [STAThread]
  static void Main(string[] args)
  {
   Console.WriteLine("Starting...");
   MyDataReader.InsertRow(73,"Гпфгпф");
   Console.WriteLine("Insert is done.");
   MyDataReader.ReadMyData();
   Console.WriteLine("Press any key to continiue...");
   Console.ReadLine();
   Console.WriteLine("... done.");
  }
 }
}

Now the actual class that gets to the database:

using System;
using System.Data.SqlClient;

namespace GetData
{
 /// <summary>
 ///
 /// </summary>
 public class MyDataReader
 {
  public MyDataReader()
  {
  }

// SQLEXPRESS is used for
// the free SQL Express edition of SQL Server
  private static String myConnectionString = "Database=sample;Server=MyMachine\\SQLEXPRESS;Integrated Security=SSPI;";

  public static void InsertRow(int id, string str)
  {
   try
   {
    SqlConnection myConnection = new SqlConnection(myConnectionString);
    string myInsertQuery = "INSERT INTO SampleTable (id, str) Values('"+id.ToString()+"', '"+str+"')";
    SqlCommand myCommand = new SqlCommand(myInsertQuery);
    myCommand.Connection = myConnection;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myCommand.Connection.Close();
   }
   catch (Exception e)
   {
    Console.WriteLine(e.ToString());
   }
  }

  public static void ReadMyData()
  {
   string mySelectQuery = "SELECT id, str FROM SampleTable";
   SqlConnection myConnection = new SqlConnection(myConnectionString);
   SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader();
   try
   {
    while (myReader.Read())
    {
     Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
    }
   }
   finally
   {
    // always call Close when done reading.
    myReader.Close();
    // always call Close when done reading.
    myConnection.Close();
   }
  }
 }
}

What you want is ReadMyData() method. Drop both files into the command-line C# program project in Visual Studio (Class1.cs instead of Class1.cs generated by Visual Studio), put the name of you machine into the connect string (and name of SQL Server instance too), compile, run. You are done. Of course, you need SQL Server installed, database sample, and table SampleTable with two fields – “id” and “str” in it.. You can use SQL Server on another machine, but see that it is configured to accept connections from non-local machine and use Windows authentications (that’s what SSPI stands fro in the connection string).

Good luck!