|
ProgrammaSubito.it
|
|
|
|
|
|
|
|
|
|
|
|
Accesso ai Dati con ObjectDataSource
File Default.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<script language="javascript" type="text/javascript">
function conferma()
{
if(!confirm('Confirm?')) return false;
return true;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<h3>ObjectDataSource Esempio</h3>
<asp:Label id="Msg" runat="server" ForeColor="Red" />
<asp:ObjectDataSource
ID="ObjectDataSourceEmployees"
runat="server"
SelectMethod="GetAllEmployees"
DeleteMethod="DeleteEmployee"
UpdateMethod="UpdateEmployee"
InsertMethod="InsertEmployee"
TypeName="ClassData"
SortParameterName="SortColumns"
StartRowIndexParameterName="StartRecord"
MaximumRowsParameterName="MaxRecords"
SelectCountMethod="SelectCount"
EnablePaging="True"
ondeleted="ObjectDataSourceEmployees_Deleted"
onupdated="ObjectDataSourceEmployees_Updated"
oninserted="ObjectDataSourceEmployees_Inserted"
oninserting="ObjectDataSourceEmployees_Inserting">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox_Cognome"
Name="filtroCognome"
PropertyName="Text"
Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
</InsertParameters>
</asp:ObjectDataSource>
<br />
<asp:Label ID="Label1" runat="server" Text="Cognome" /><br />
<asp:TextBox ID="TextBox_Cognome" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<br /><br />
<table cellspacing="10">
<tr>
<td valign="top">
<asp:GridView ID="EmployeesGridView"
DataSourceID="ObjectDataSourceEmployees"
DataKeyNames="EmployeeID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
PageSize="3"
RunAt="server"
EmptyDataText="Non ci sono Elementi"
CellPadding="4"
ForeColor="#333333"
GridLines="None">
<FooterStyle BackColor="#507CD1"
Font-Bold="True"
ForeColor="White" />
<PagerStyle BackColor="#2461BF"
ForeColor="White"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1"
Font-Bold="True"
ForeColor="#333333" />
<HeaderStyle backcolor="#507CD1"
forecolor="White"
Font-Bold="True"/>
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Delete" Text="Delete" OnClientClick="return conferma()">
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowSelectButton="True" />
<asp:TemplateField HeaderText="Employee ID" SortExpression="EmployeeID">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("EmployeeID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</td>
</tr>
</table>
<br /><br />
<asp:TextBox ID="TextBoxNome_Ins"
MaxLength="30"
runat="server" />
<asp:TextBox ID="TextBoxCognome_Ins"
MaxLength="30"
runat="server" />
<asp:Button ID="Button_Inserisci"
runat="server"
Text="Inserisci"
onclick="Button_Inserisci_Click" />
<br /><br /><br />
<asp:Label ID="Label_NewID" runat="server" />
<br /><br />
</form>
</body>
</html>
File Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Msg.Text = "";
}
protected void ObjectDataSourceEmployees_Deleted(object sender, ObjectDataSourceStatusEventArgs e)
{
if ((int)e.ReturnValue == 0)
Msg.Text = "Employee was not deleted. Please try again.";
else
EmployeesGridView.DataBind();
}
protected void ObjectDataSourceEmployees_Updated(object sender, ObjectDataSourceStatusEventArgs e)
{
EmployeesGridView.DataBind();
}
protected void Button_Inserisci_Click(object sender, EventArgs e)
{
ObjectDataSourceEmployees.Insert();
}
protected void ObjectDataSourceEmployees_Inserting(object sender, ObjectDataSourceMethodEventArgs e)
{
e.InputParameters["FirstName"] = TextBoxNome_Ins.Text;
e.InputParameters["LastName"] = TextBoxCognome_Ins.Text;
}
protected void ObjectDataSourceEmployees_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
string newID = e.ReturnValue.ToString();
ObjectDataSourceEmployees.DataBind();
TextBoxNome_Ins.Text = "";
TextBoxCognome_Ins.Text = "";
Label_NewID.Text = newID;
}
protected void Button1_Click(object sender, EventArgs e)
{
EmployeesGridView.DataBind();
}
}
File /App_Code/ClassData.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
public class ClassData
{
private string _connectionString;
public ClassData()
{
Inizializza();
}
public void Inizializza()
{
if (ConfigurationManager.ConnectionStrings["ConnectionStringLocale"] == null ||
ConfigurationManager.ConnectionStrings["ConnectionStringLocale"].ConnectionString.Trim() == "")
{
throw new Exception("Connection string 'ConnectionStringLocale' inesistente");
}
_connectionString =
ConfigurationManager.ConnectionStrings["ConnectionStringLocale"].ConnectionString;
}
public DataTable GetAllEmployees(string sortColumns, int startRecord, int maxRecords, string filtroCognome)
{
string sqlQuery = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees ";
if (filtroCognome != null && filtroCognome.Trim() != "")
{
sqlQuery += " WHERE UPPER(LastName) LIKE @filtroCognome ";
}
if (sortColumns.Trim() == "")
sqlQuery += "ORDER BY EmployeeID";
else
sqlQuery += "ORDER BY " + sortColumns;
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand(sqlQuery, conn);
if (filtroCognome != null && filtroCognome.Trim() != "")
{
command.Parameters.Add("@filtroCognome", SqlDbType.VarChar, 20).Value = filtroCognome.ToUpper() + "%";
}
adapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
conn.Open();
adapter.Fill(ds, startRecord, maxRecords, "Employees");
}
catch (SqlException e)
{
throw new Exception("ClassData - GetAllEmployees:" + e.Message);
}
finally
{
conn.Close();
}
return ds.Tables["Employees"];
}
public int SelectCount(string filtroCognome)
{
string sqlQuery = "SELECT COUNT(*) FROM Employees ";
if (filtroCognome != null && filtroCognome.Trim() != "")
{
sqlQuery += " WHERE UPPER(LastName) LIKE @filtroCognome ";
}
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
if (filtroCognome != null && filtroCognome.Trim() != "")
{
cmd.Parameters.Add("@filtroCognome", SqlDbType.VarChar, 20).Value = filtroCognome.ToUpper() + "%";
}
int result = 0;
try
{
conn.Open();
result = (int)cmd.ExecuteScalar();
}
catch (SqlException e)
{
throw new Exception("ClassData - SelectCount:" + e.Message);
}
finally
{
conn.Close();
}
return result;
}
public int DeleteEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", conn);
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
throw new Exception("ClassData - DeleteEmployee:" + e.Message);
}
finally
{
conn.Close();
}
return result;
}
public int UpdateEmployee(int EmployeeID, string LastName, string FirstName)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Employees " +
" SET FirstName=@FirstName, LastName=@LastName " +
" WHERE EmployeeID=@EmployeeID", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
throw new Exception("ClassData - UpdateEmployee:" + e.Message);
}
finally
{
conn.Close();
}
return result;
}
public int InsertEmployee(string LastName, string FirstName)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
" (FirstName, LastName) " +
" Values(@FirstName, @LastName); " +
"SELECT @EmployeeID = SCOPE_IDENTITY()", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
newEmployeeID = (int)p.Value;
}
catch (SqlException e)
{
throw new Exception("ClassData - InsertEmployee:" + e.Message);
}
finally
{
conn.Close();
}
return newEmployeeID;
}
}
File
/App_Data/Database.mdf
File /web.config
<?xml version="1.0"?>
<configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionStringRemota"
connectionString="Data Source=192.168.1.3;
User ID=xxxx;Password=xxxx;
Initial Catalog=Northwind;" />
<add name="ConnectionStringLocale"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\Database.mdf;
Integrated Security=True;User Instance=True"/>
</connectionStrings>
.
.
.
|
|
|
|
|
|
|