As an exampleà
SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
connection.Open();
SqlDataAdapter adapter1 =
new SqlDataAdapter("SELECT * FROM AntiqueOwners", connection);
DataSet ds = new DataSet();
adapter1.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
connection.Close();
But it is not the efficient way. Above example code only work with SQL server based applications. But you can write codes to work with any database applications.This is achieved using Factory classes
.
· First you need to use System.Data.Common namespace.
· Then create a factory for you db access
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLClient");
Note: System.Data.SQLClient is the provider name for sql server based DB.if you use another DB type use relevant provider names.
· Set the connection of factory
DbConnection con = factory.CreateConnection();
· Define the connection string of connection.
in here I used Application settings for store connections.for more details about application settings please refer, http://msdn.microsoft.com/en-us/library/aa730869(v=vs.80).aspx
· Then create command using the factory class and define command text and connection for that command
DbCommand cmd = factory.CreateCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM AntiqueOwners";
· If you need to use any parameters with your command text you can use DBParameters
DbParameter para = factory.CreateParameter();
para.ParameterName = "@id";
para.DbType = DbType.Int32;
para.Value = id;
cmd.Parameters.Add(para);
cmd.CommandText = " INSERT INTO AntiqueOwners(ID,Name) VALUES(@id,'ABC')";
· Then you can use adapter methods or readers to fill data into data tables or data grid or you can simply execute your query.
Ø Execute an SQL Command that does not return any value
try
{
con.Open();
cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
Ø Execute command that returns value
try
{
con.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
double height = reader.GetDouble(2);
// Process the values
}
}
finally
{
con.Close();
}
Ø Fill data to data grid
DbDataAdapter adapter = factory.CreateDataAdapter();
Co.Open();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
