SQL connection example

A simple example that shows how to access a SQL database by using the System.Data.SqlClient library.

Snippet information

Author:
Jonas John

License:
Public domain

Language:
C#

Created:
08/09/2007

Updated:
08/09/2007

Tags:
database functions, sql, example

Related links:
- ConnectionStrings.com
- SqlConnection Class

// This example needs the 
// System.Data.SqlClient library
 
#region Building the connection string
 
string Server = "localhost";
string Username = "my_username";
string Password = "my_password";
string Database = "my_database";
 
string ConnectionString = "Data Source=" + Server + ";";
ConnectionString += "User ID=" + Username + ";";
ConnectionString += "Password=" + Password + ";";
ConnectionString += "Initial Catalog=" + Database;
 
#endregion
 
 
#region Try to establish a connection to the database
 
SqlConnection SQLConnection = new SqlConnection();
 
try
{
    SQLConnection.ConnectionString = ConnectionString;
    SQLConnection.Open();
 
    // You can get the server version 
    // SQLConnection.ServerVersion
}
catch (Exception Ex)
{
    // Try to close the connection
    if (SQLConnection != null)
        SQLConnection.Dispose();
 
    // Create a (useful) error message
    string ErrorMessage = "A error occurred while trying to connect to the server.";
    ErrorMessage += Environment.NewLine;
    ErrorMessage += Environment.NewLine;
    ErrorMessage += Ex.Message;
 
    // Show error message (this = the parent Form object)
    MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
 
    // Stop here
    return;
}
 
#endregion
 
 
#region Execute a SQL query
 
string SQLStatement = "SELECT * FROM ExampleTable";
 
// Create a SqlDataAdapter to get the results as DataTable
SqlDataAdapter SQLDataAdapter = new SqlDataAdapter(SQLStatement, SQLConnection);
 
// Create a new DataTable
DataTable dtResult = new DataTable();
 
// Fill the DataTable with the result of the SQL statement
SQLDataAdapter.Fill(dtResult);
 
// Loop through all entries
foreach (DataRow drRow in dtResult.Rows)
{
    // Show a message box with the content of 
    // the "Name" column
    MessageBox.Show(drRow["Name"].ToString());
}
 
// We don't need the data adapter any more
SQLDataAdapter.Dispose();
 
#endregion
 
 
#region Close the database link
 
SQLConnection.Close();
SQLConnection.Dispose();
 
#endregion

Found a bug? Or do you have a better solution for this?
Feel free to leave a message:

Add a comment

Leave a comment

Your name:

Url:

Send replies to this email adress:
(if provided, email will not be displayed or shared)

Comment:

Homepage:

imran August 19, 2010 at 09:40

easy one

riya August 12, 2010 at 13:25

Good One!!!
Helped a lot!!!!

degitu August 08, 2010 at 13:22

It is nice!

nXqd June 13, 2010 at 04:22

Nice and simple, thanks so much :)

Shahriar May 31, 2010 at 16:59

Check this link ( http://www.shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html ) for details on how to connect to SQL server database from C#.NET database applications as well as Java database applications. It also describes how to pass embedded SQL queries, calling stored procedures, pass parameter etc.

Mhammad May 11, 2010 at 12:48

For those who said this is a bad code, eat shit u started like this so **** off.

sujeet April 15, 2010 at 08:38

very good post for the beginners, it really helps me a lot....

Juntao March 18, 2010 at 21:28

horrible code?! lmao

Kelvin January 20, 2010 at 16:01

That is fantastic, thank you. :-)
Been looking for something that would show me how to use a datatable :-)

I was looking for a way to do multiple tables from SQL and reading them in the application :-)

Yay !!!

JordanC May 21, 2009 at 13:55

Oh lord, that code below is horrible.

Impact May 13, 2009 at 20:55

Also for the delete ,update ,edit button ive wrote some codes ,wich i think they are good enough.
#region refresh button
private void button2_Click(object sender, EventArgs e)
{
textBox4.Clear();
string queryString = "SELECT database, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID";
loadDataGrid(queryString);
}
#endregion

#region Input
private void button6_Click(object sender, EventArgs e)
{
string typeString;
try
{
typeString = comboBox1.SelectedItem.ToString();
}
catch (Exception ex) {
MessageBox.Show("You must enter movie typenError: " + ex.Message + "");
return;
}
int type = 0;
string name = textBox1.Text.ToString();
string publisher = textBox2.Text.ToString();
string year = textBox3.Text.ToString();
int yr = 0;
if (year != "")
{
yr = CheckYear(year);
}
string previewed;
if (radioButton1.Checked == true)
{
previewed = "Yes";
}
else
{
previewed = "No";
}
if (yr != 1)
{
if (typeString == "Adventure") type = 1;
if (typeString == "Comedy") type = 2;
if (typeString == "Action") type = 3;
if (typeString == "Cartoon") type = 4;
if (typeString == "Romantic") type = 5;
if (typeString == "Fantasy") type = 6;
if (typeString == "Thriller") type = 7;
if (typeString == "Historic") type = 8;
if (typeString == "Drama") type = 9;
if (typeString == "Horor") type = 10;
if (typeString == "Sci-Fi") type = 11;
if (typeString == "Crime") type = 12;
if (typeString == "Biografy") type = 13;
if (typeString == "Documentary") type = 14;
string SQLString ="";

if (year == "")
{
SQLString = "INSERT INTO movie(Title, Publisher, Previewed, typeID) VALUES('" + name.Replace("'", "''") + "','" + publisher + "','" + previewed + "'," + type + ");";
}
else
{
MessageBox.Show(yr.ToString());
SQLString = "INSERT INTO movie(Title, Publisher, Previewed, MovieYear, typeID) VALUES('" + name.Replace("'", "''") + "','" + publisher + "','" + previewed + "'," + yr + "," + type + ");";
}

OleDbCommand SQLCommand = new OleDbCommand();
SQLCommand.CommandText = SQLString;
SQLCommand.Connection = database;
int response = -1;
try
{
response = SQLCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (response >= 1) MessageBox.Show("Movie is added to database","Successful",MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
comboBox1.ResetText();
radioButton1.Checked = radioButton2.Checked = false;
}
else
{
MessageBox.Show("The year format is not correct!nPlease try to pick a valid year.", "Warning",MessageBoxButtons.OK, MessageBoxIcon.Warning);
textBox3.Clear();
textBox3.Focus();
}
}

public int CheckYear(string year)
{
int yr = int.Parse(year);
if (yr >= 2100 || yr <= 1900)
{
return 1;
}
else
{
return yr;
}
}

#endregion

#region Delete/Edit button handling
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

string queryString = "SELECT database, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID";

int currentRow = int.Parse(e.RowIndex.ToString());
try
{
string databaseString = dataGridView1[0, currentRow].Value.ToString();
databaseInt = int.Parse(databaseString);
}
catch (Exception ex) { }
// edit button
if (dataGridView1.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
{
string title = dataGridView1[1, currentRow].Value.ToString();
string publisher = dataGridView1[2, currentRow].Value.ToString();
string previewed = dataGridView1[3, currentRow].Value.ToString();
string year = dataGridView1[4, currentRow].Value.ToString();
string type = dataGridView1[5, currentRow].Value.ToString();
//runs form 2 for editing
Form2 f2 = new Form2();
f2.title = title;
f2.publisher = publisher;
f2.previewed = previewed;
f2.year = year;
f2.type = type;
f2.database = databaseInt;
f2.Show();
dataGridView1.Update();

}
// delete button
else if (dataGridView1.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
{
// delete sql query
string queryDeleteString = "DELETE FROM movie where database = "+databaseInt+"";
OleDbCommand sqlDelete = new OleDbCommand();
sqlDelete.CommandText = queryDeleteString;
sqlDelete.Connection = database;
sqlDelete.ExecuteNonQuery();
loadDataGrid(queryString);
}

}
Among this i ve introduced a refresh button in order to reactualizate the list from the table chosen to be shown in the datagridview.

Impact May 13, 2009 at 20:47

i ve used a database that consisted in only 2 tables,of course you can make a lot more but youll have to modify the sql language and some of the programing code.
to load the datagridview that i used to show the list from one able i used the following code:
public void loadDataGrid(string sqlQueryString) {

OleDbCommand SQLQuery = new OleDbCommand();
DataTable data = null;
dataGridView1.DataSource = null;
SQLQuery.Connection = null;
OleDbDataAdapter dataAdapter = null;
dataGridView1.Columns.Clear(); // <-- clear columns
//---------------------------------
SQLQuery.CommandText = sqlQueryString;
SQLQuery.Connection = database;
data = new DataTable();
dataAdapter = new OleDbDataAdapter(SQLQuery);
dataAdapter.Fill(data);
dataGridView1.DataSource = data;
dataGridView1.AllowUserToAddRows = false; // remove the null line
dataGridView1.ReadOnly = true;
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].Width = 340;
dataGridView1.Columns[3].Width = 55;
dataGridView1.Columns[4].Width = 50;
dataGridView1.Columns[5].Width = 80;
// insert edit button into datagridview
editButton = new DataGridViewButtonColumn();
editButton.HeaderText = "Edit";
editButton.Text = "Edit";
editButton.UseColumnTextForButtonValue = true;
editButton.Width = 80;
dataGridView1.Columns.Add(editButton);
// insert delete button to datagridview
deleteButton = new DataGridViewButtonColumn();
deleteButton.HeaderText = "Delete";
deleteButton.Text = "Delete";
deleteButton.UseColumnTextForButtonValue = true;
deleteButton.Width = 80;
dataGridView1.Columns.Add(deleteButton);
}
P.S. :i used two buttons in the datagridview that appear in each row ,in order to delete or to edit the data directly from the interface into the database.

Impact May 13, 2009 at 20:40

after analyzing this code source,i thought that it would be good ideea to make a full conection sequence. I took a database made in Microsoft Access,and i tried to make a C#Interface for it.Here's what it looks like:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb; // <- for database methods

namespace Database
{
public partial class Form1 : Form
{
public OleDbConnection database;
DataGridViewButtonColumn editButton;
DataGridViewButtonColumn deleteButton;
int DatabaseInt;

#region Form1 constructor
public Form1()
{

InitializeComponent();
// initiate DB connection
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb";
try
{

database = new OleDbConnection(connectionString);
database.Open();
//SQL query to the list
string queryString = "here you must use the sql language in order to access you desired database";
loadDataGrid(queryString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
#endregion

JordanC May 12, 2009 at 15:12

Why are you neglecting to use SqlException? Your example just gives a very unspecific message.

Jack P November 06, 2008 at 21:14

I am making a simple Instant Messenger to develop my C# skills, i added this code to the 'Sign in' button - entered the correct database, username and password but i keep getting error message that it can not connect to the SQL database - I use XAMPP which has PHPmyadmin built-in (i store my databases in there and it works for everything else i use) would i have to add in a MySQL connector like 'MySQL ODBC Connector 5.1' or something?

Thanks.

Danijel September 23, 2008 at 08:38

Ok, if I knew it was simple as that, I wouldn't ask :)

Convert.ToInt32(drRow["ID"].ToString());

Danijel September 23, 2008 at 08:27

how do you use drRow["ID"]; to convert it to a number since its an object?

qwerty September 16, 2008 at 10:05

huso

qwe July 10, 2008 at 12:46