public DataTable GetDataTable(Command command)
{
DbDataAdapter da = _fabrique.CreateDataAdapter();
da.SelectCommand = this.SetCommand(command);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
protected void Page_Load(object sender, EventArgs e)
{
this.conn = new Connection(ConfigurationManager.ConnectionStrings["AdventureWorks2012"].ProviderName, @"Data Source=FORMA-VDI1208\TFTIC;Initial Catalog=AdventureWorks2012;Integrated Security=True");
Command cmd = new Command("select top 20 BusinessEntityID, FirstName + ' ' + LastName as FullName from Person.Person order by BusinessEntityID");
DataTable dt = this.conn.GetDataTable(cmd);
Session["CUSTOMER"] = dt;
DGPersons.DataSource = dt;
DGPersons.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Command cmd = new Command("select ProductSubcategoryID, Name as CategoryName from Production.ProductSubcategory");
DataTable dt = conn.GetDataTable(cmd);
DdlCategories.DataSource = dt;
DdlCategories.DataTextField = "CategoryName";
DdlCategories.DataValueField = "ProductSubcategoryID";
DdlCategories.DataBind();
}
}
protected void DdlCategories_SelectedIndexChanged(object sender, EventArgs e)
{
string param = DdlCategories.SelectedValue;
Command cmd = new Command("select prod.ProductID, prod.ProductNumber, prod.Name, prix.ListPrice from Production.Product prod join Production.ProductListPriceHistory prix on prod.ProductID = prix.ProductID where prod.ProductSubcategoryID = @catID AND prix.EndDate is null order by ProductID");
cmd.AddParameter("@catID", param);
DataTable dt = conn.GetDataTable(cmd);
LabelNbrResults.Text = string.Format("{0} produits trouvés", dt.Rows.Count.ToString());
productListRepeater.DataSource = dt;
productListRepeater.DataBind();
}
public object ExecuteScalar(Command command)
{
object o = null;
try
{
this._connection.Open();
DbCommand c = this.SetCommand(command);
o = c.ExecuteScalar();
}
finally
{
this._connection.Close();
}
return o;
}
public int ExecuteNonQuery(Command command)
{
int rows = -1;
try
{
this._connection.Open();
DbCommand c = this.SetCommand(command);
rows = c.ExecuteNonQuery();
}
finally
{
this._connection.Close();
}
return rows;
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
Command cmd = new Command("select last_name + ' ' + first_name as full_name from student");
if (!this.Page.IsPostBack)
{
DataTable dt = this.conn.GetDataTable(cmd);
DdlStudents.DataSource = dt;
DdlStudents.DataTextField = "full_name";
DdlStudents.DataBind();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Transforme un objet de type Command en objet de type SqlCommand
/// </summary>
/// <param name="command">un objet de type Command</param>
/// <returns>un objet de type SqlCommand</returns>
private DbCommand SetCommand(Command command)
{
DbCommand c = this._connection.CreateCommand();
c.CommandText = command.Query;
c.CommandType = command.IsStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
foreach (KeyValuePair<string, object> kvp in command.Parameters)
{
DbParameter Param = _fabrique.CreateParameter();
Param.ParameterName = kvp.Key;
Param.Value = (kvp.Value != null) ? kvp.Value : DBNull.Value;
c.Parameters.Add(Param);
}
return c;
}