Retrieval of Data from Sql Data Base …

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.OleDb;

namespace WindowsFormsApplication17
{
public partial class Form1 : Form
{
public OleDbConnection conn = new OleDbConnection();

public Form1()
{
InitializeComponent();

string s = “provider= Microsoft.jet.oledb.4.0; data source= E:\\UIT\\Visual Programming CS201 by Syed Faisal Ali\\Week 7\\Nwind.mdb”;
conn.ConnectionString = s;
conn.Open();
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{

OleDbCommand cmd = new OleDbCommand();

//Assign SQL statement to the command object
cmd.CommandText = “select LastName from Employees”;
// Define the type of execution , TEXT means SQL Statement
cmd.CommandType = CommandType.Text;
// Assign active open connection to the command, so that the command will be executed on a particular connection

cmd.Connection = conn;

// Reader is used to hold data that is retured by the SQL query. The data is returned when the command is executed .
OleDbDataReader r = cmd.ExecuteReader();
// Loop until data is available in the Reader , one by one
while (r.Read())
{
// Add each record to the comboBox
comboBox1.Items.Add(r.GetString(0));

}

// Closed  reader then connection, to released the memory hold by these objects
r.Close();
conn.Close();
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{

// open conection Because it was closed earlier
conn.Open();
// Define another Command object
OleDbCommand cmdS = new OleDbCommand();

// We want to select the name of the customers , who’s ID is been selected at the ComboBox by the user.
cmdS.CommandText = “select FirstName from Employees where LastName='” + comboBox1.Text + “‘”;

cmdS.CommandType = CommandType.Text;
cmdS.Connection = conn;
OleDbDataReader r1 = cmdS.ExecuteReader();
while (r1.Read())
{
// Fill the textbox by the data returned by the SQL Query against the Customer ID
textBox1.Text = r1.GetString(0);
}
r1.Close();
conn.Close();

}
}

}

Simple ADO.Net Login

ADO.NET is a large set of .NET classes that enable us to retrieve and manipulate data, and update data sources, in very many different ways. As an essential part of the .NET framework, it shares many of its features: features such as multi-language support, garbage collection, just-in-time compilation, object-oriented design, and dynamic caching, and is far more than an upgrade of previous versions of ADO. ADO.NET is set to become a core component of any data-driven .NET application or Web Service, and understanding its power will be essential to anyone wishing to utilize .NET data maintain to maximum effect.

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;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
OleDbConnection con = new OleDbConnection(@”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\shahrooz\Desktop\login.accdb;Persist Security Info=True”);
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
con.Open();
OleDbCommand cmd = new OleDbCommand(“select * from login where username ='” + maskedTextBox1.Text + “‘”, con);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (maskedTextBox1.Text == dr[0].ToString())
{
if (maskedTextBox2.Text == dr[1].ToString())
{
MessageBox.Show(“i Sucesss “);
}
else
{
MessageBox.Show(“Incorrect Password”, “Information”);
}
}
}
con.Close();
dr.Close();
}
}
}

How To Delete Values From Database Using SQL In C#…

Now in our early post we learned how to insert the values in the database from coding in C#.

Once you insert a value in the database you should also know how to delete the values from the database.

Deleting is considered to be the easiest thing in SQL, It just takes few lines of code to perform this task but there are some preliminary steps which are involved before performing any SQL query in C#.

Beside the easiness of this query remember the delete query is very power full query and once it is executed then recovering the data is now a piece of cake. Always be fully concentrated while using this query and never write the wrong table name or column name because C# does not show any errors during the compile time,If you have written a wrong query then you have to wait until the code is executed.

There is also a difference between the delete and drop table query for the difference visit our previous blog http://www.farazashraf.wordpress.com. Now lets start the deletion process.

First we will add the namespace of SQL in our code…

So to add namespace first thing you should know is that your data is on which database platform.

Let say you are working on simple Microsoft platform and your data is on access database then you would be using the OLE DB (Object Linking and Embedding, Database) this is an API designed by Microsoft to work on access database.

STEP 1:

So you will start with

Using System;

Using System.Data.OLeDb;

STEP 2:

after adding the namespace now its time for coding in main.

First you  have to create database connection so it will be

OLeDbConnection conn=new OLeDbConnection();

To set the connection string for the connection you need to have a database link. You can either find this while connection database in visual studio or

you can write this connection string.

this is the connection string for access database 2007.

String Connection=(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Files\database1.accdb;”)

For all the connection strings you can visit my previous post on this blog.
www.farazahraf.wordpress.com

STEP3:

To connect OleDb connection with the connection string you can use this line of code.

conn.ConnectionString=connection;

where conn is the object name OLeDb connection class created above you can use any name.

while connection is string name you can also use any name for this word.

STEP4:

Now coming to our main topic of deleting values in the database.
so for this you will have to open connection with database by using this command.

conn.Open();

this will open connection with the database.

now we will write the query for deletion of values in the database.

OLeDBCommand cmd =new OLeDbCommand(“DELETE FROM TABLE1 WHERE ID=001″,conn) ;
cmd.ExecuteNonQuery();

conn.Close();

The (command) class is used here to insert the commands in the data base which will be executed. Query contains a table name which is  “TABLE1″.

“WHERE” clause is used to specify values from which column and which row should be deleted.

Here Column name is “ID” you can specify any column but mostly column with the primary key is used to delete the values from the table.

The id from the values will be deleted is “001” you can use any key to delete.

*Remember if you will not specify the where clause then every row will be deleted from the table so be aware before using the delete query because this is a very power full query and is hard to recover data back.

Remember to always close the connection with con.Close(); after you are done with the deletion or any other command.

I hope you would have liked this.
For any queries regarding database please comment . I will be pleased to answer your queries …

How to insert values in data base using sql queries…

Now many folks out there are worry that how to insert values in database using SQL queries in C#. Inserting values  while working on oracle would be easy with just a single query but for some people it gets difficult while inserting values from the code in C#.

Now its not too hard as it sounds in fact its very easy. We will first have to add the required namespace for the database we are using and then we start with coding the insert query.

So to add namespace first thing you should know is that your data is on which database platform.

Let say you are working on simple Microsoft platform and your data is on access database then you would be using the OLE DB (Object Linking and Embedding, Database) this is an API designed by Microsoft to work on access database.

STEP 1:

So you will start with

Using System;

Using System.Data.OLeDb;

STEP 2:

after adding the namespace now its time for coding in main.

First you  have to create database connection so it will be

OLeDbConnection conn=new OLeDbConnection();

To set the connection string for the connection you need to have a database link. You can either find this while connection database in visual studio or

you can write this connection string.

this is the connection string for access database 2007.

String Connection=(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Files\database1.accdb;”)

For all the connection strings you can visit my previous post on this blog.
www.farazahraf.wordpress.com

STEP3:

To connect OleDb connection with the connection string you can use this line of code.

conn.ConnectionString=connection;

where conn is the object name OLeDb connection class created above you can use any name.

while connection is string name you can also use any name for this word.

STEP4:

Now coming to our main topic of inserting values in the database.
so for this you will have to open connection with database by using this command.

conn.Open();

this will open connection with the database.

now we will write the query for database insertion.

OLeDBCommand cmd =new OLeDbCommand(“INSERT INTO TABLE1 (SID,SNAME,GPA) Values (‘”Txt_Id”‘,'”Faraz”‘,”Numaric_GPA”),conn);
cmd.ExecuteNonQuery();

conn.Close();

The command class is used here to insert the command in the data base which will be executed. Query contains a table name which is  “TABLE1” 3 columns name where we need to insert data and values contains the actual data from the program to be inserted into database. “Txt_Id” is the name of the text box that contains the id. you can use any control name here or you can also add values of your own as i have inserted my name directly instead of the text box control name. Now apostrophe  around the first 2 values would be bit surprising but i have used apostrophe to show that these values are string in the database and the GPA is not string so apostrophe is not used with it.

Remember to always close the connection with con.Close(); after you are done with the insertion or any other command.

I hope you would have liked this.
For any queries regarding database please comment . I will be pleased to answer your queries …