Working with SQL

[Patrick_Neville]

Please tell me how to work with SQL databases in C#

[kvprajapati]

Text from the MSDN - Overview of ADO.NET

SUMMARY: ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML.

[sknake]

Here is an example of databinding a grid to the results of executing the SQL sproc sp_who with the login of "sa". It demonstrates a few of concepts:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace daniweb
{
  public partial class frmSprocView : Form
  {
    private DataTable dt;

    public frmSprocView()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string sp_name = "sp_who";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sp_name, conn))
        {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Add(new SqlParameter("@loginame", SqlDbType.VarChar)).Value = "sa";
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            if (dt != null)
              dt.Dispose();
            dt = new DataTable();
            dt.Load(dr);
          }
        }
      }
      dataGridView1.DataSource = dt; //data bind

      string[,] arr = new string[dataGridView1.Rows.Count, dataGridView1.Columns.Count];
      for (int y = 0; y < dt.Rows.Count; y++)
      {
        for (int x = 0; x < dt.Columns.Count; x++)
        {
          arr[y, x] = Convert.ToString(dt.Rows[y][x]);
        }
      }

      System.Diagnostics.Debugger.Break();
    }


  }
}