Saturday, August 26, 2006

How to handle formview and gridview when underlying table is empty?

objectdatasource is used here.




private bool LoadDataEmpty
{
get { return (bool)(ViewState["LoadDataEmpty"] ?? false); }
set { ViewState["LoadDataEmpty"] = value; }
}

protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
// bubble exceptions before we touch e.ReturnValue
if (e.Exception != null)
{
throw e.Exception;
}
// get the DataTable from the ODS select mothod
DataTable dataTable = (DataTable)((DataSet)e.ReturnValue).Tables[0];

// if rows=0 then add a dummy (null) row and set the LoadDataEmpty flag.
if (dataTable.Rows.Count == 0)
{
DataRow row = dataTable.NewRow();
row[0] = 1;
row[1] = "";
row[2] = "";
row[3] = "";
row[4] = "";
row[5] = DateTime.Now.ToString();
dataTable.Rows.Add(row);
LoadDataEmpty = true;
}
else
{
LoadDataEmpty = false;
}

}
protected void EditGrid_RowCreated(object sender, GridViewRowEventArgs e)
{
if (LoadDataEmpty && e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Visible = false;
e.Row.Controls.Clear();
}
}

Friday, August 25, 2006

web.config


connectionString
add name="FlowerConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\flower.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"



connection
SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["FlowerConnectionString"].ConnectionString);




public static SqlDataAdapter CreateCustomerAdapter(
SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();

// Create the SelectCommand.
SqlCommand command = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", connection);

// Add the parameters for the SelectCommand.
command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
command.Parameters.Add("@City", SqlDbType.NVarChar, 15);

adapter.SelectCommand = command;

// Create the InsertCommand.
command = new SqlCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", connection);

// Add the parameters for the InsertCommand.
command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

adapter.InsertCommand = command;

// Create the UpdateCommand.
command = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection);

// Add the parameters for the UpdateCommand.
command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter parameter = command.Parameters.Add(
"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original;

adapter.UpdateCommand = command;

// Create the DeleteCommand.
command = new SqlCommand(
"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

// Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add(
"@CustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original;

adapter.DeleteCommand = command;

return adapter;
}



public void DeleteUser(string user_id)
{
InitUser();
DataRow workRow = _dsUsers.Tables[0].Rows.Find(user_id);
workRow.Delete();
_daUsers.Update(_dsUsers,"users");
_dsUsers.AcceptChanges();
TermUser();
//dsUsers.WriteXml(filePath, XmlWriteMode.WriteSchema);
}