private void DataPortal_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
private void Child_Fetch(OrderStatusCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [OrderId], [LineNum], [Timestamp], [Status] FROM [dbo].[OrderStatus] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Business.OrderStatus.GetOrderStatus(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
private void DataPortal_Fetch(OrderCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("[dbo].[CSLA_Order_Select]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
command.Parameters.AddWithValue("@p_ShipAddr2HasValue", criteria.ShipAddr2HasValue);
command.Parameters.AddWithValue("@p_BillAddr2HasValue", criteria.BillAddr2HasValue);
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Tests.StoredProcedures.Order.GetOrder(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
private void Child_Fetch(AccountCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [AccountId], [UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone] FROM [dbo].[Account] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Business.Account.GetAccount(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
private void DataPortal_Fetch(ProfileCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [UniqueID], [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate] FROM [dbo].[Profiles] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Business.Profile.GetProfile(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
/// <summary>
/// Fetch ProfileList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public ProfileList Fetch(ProfileCriteria criteria)
{
ProfileList item = (ProfileList)Activator.CreateInstance(typeof(ProfileList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
string commandText = String.Format("SELECT [UniqueID], [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate] FROM [dbo].[Profiles] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
do
{
item.Add(new ProfileFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
OnFetched();
return item;
}
private void Child_Fetch(SupplierCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [SuppId], [Name], [Status], [Addr1], [Addr2], [City], [State], [Zip], [Phone] FROM [dbo].[Supplier] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Tests.ParameterizedSQL.Supplier.GetSupplier(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
/// <summary>
/// Fetch CartList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public CartList Fetch(CartCriteria criteria)
{
CartList item = (CartList)Activator.CreateInstance(typeof(CartList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("[dbo].[CSLA_Cart_Select]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
item.Add(new CartFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
MarkAsChild(item);
OnFetched();
return item;
}
private void DataPortal_Fetch(OrderCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [OrderId], [UserId], [OrderDate], [ShipAddr1], [ShipAddr2], [ShipCity], [ShipState], [ShipZip], [ShipCountry], [BillAddr1], [BillAddr2], [BillCity], [BillState], [BillZip], [BillCountry], [Courier], [TotalPrice], [BillToFirstName], [BillToLastName], [ShipToFirstName], [ShipToLastName], [AuthorizationNumber], [Locale] FROM [dbo].[Orders] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Tests.ParameterizedSQL.Order.GetOrder(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
private void DataPortal_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Business.Category.GetCategory(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
/// <summary>
/// Fetch OrderList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public OrderList Fetch(OrderCriteria criteria)
{
OrderList item = (OrderList)Activator.CreateInstance(typeof(OrderList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
string commandText = String.Format("SELECT [OrderId], [UserId], [OrderDate], [ShipAddr1], [ShipAddr2], [ShipCity], [ShipState], [ShipZip], [ShipCountry], [BillAddr1], [BillAddr2], [BillCity], [BillState], [BillZip], [BillCountry], [Courier], [TotalPrice], [BillToFirstName], [BillToLastName], [ShipToFirstName], [ShipToLastName], [AuthorizationNumber], [Locale] FROM [dbo].[Orders] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
do
{
item.Add(new OrderFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
MarkAsChild(item);
OnFetched();
return item;
}
private void DataPortal_Fetch(LineItemCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [OrderId], [LineNum], [ItemId], [Quantity], [UnitPrice] FROM [dbo].[LineItem] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Tests.ParameterizedSQL.LineItem.GetLineItem(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
/// <summary>
/// Fetch CartList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public CartList Fetch(CartCriteria criteria)
{
CartList item = (CartList)Activator.CreateInstance(typeof(CartList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
string commandText = String.Format("SELECT [CartId], [UniqueID], [ItemId], [Name], [Type], [Price], [CategoryId], [ProductId], [IsShoppingCart], [Quantity] FROM [dbo].[Cart] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
do
{
item.Add(new CartFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
MarkAsChild(item);
OnFetched();
return item;
}
/// <summary>
/// Fetch AccountList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public AccountList Fetch(AccountCriteria criteria)
{
AccountList item = (AccountList)Activator.CreateInstance(typeof(AccountList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
string commandText = String.Format("SELECT [AccountId], [UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone] FROM [dbo].[Account] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
do
{
item.Add(new AccountFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
MarkAsChild(item);
OnFetched();
return item;
}
/// <summary>
/// Fetch LineItemList.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <returns></returns>
public LineItemList Fetch(LineItemCriteria criteria)
{
LineItemList item = (LineItemList)Activator.CreateInstance(typeof(LineItemList), true);
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return item;
// Fetch Child objects.
string commandText = String.Format("SELECT [OrderId], [LineNum], [ItemId], [Quantity], [UnitPrice] FROM [dbo].[LineItem] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
do
{
item.Add(new LineItemFactory().Map(reader));
} while(reader.Read());
}
}
}
}
MarkOld(item);
OnFetched();
return item;
}
private void Child_Fetch(ItemCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
string commandText = String.Format("SELECT [ItemId], [ProductId], [ListPrice], [UnitCost], [Supplier], [Status], [Name], [Image] FROM [dbo].[Item] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Business.Item.GetItem(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
private void Child_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
RaiseListChangedEvents = false;
// Fetch Child objects.
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("[dbo].[CSLA_Category_Select]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
command.Parameters.AddWithValue("@p_NameHasValue", criteria.NameHasValue);
command.Parameters.AddWithValue("@p_DescnHasValue", criteria.DescriptionHasValue);
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
do
{
this.Add(PetShop.Tests.Collections.EditableChild.Category.GetCategory(reader));
} while(reader.Read());
}
}
}
}
RaiseListChangedEvents = true;
OnFetched();
}
protected void DataPortal_Fetch(SupplierCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [SuppId], [Name], [Status], [Addr1], [Addr2], [City], [State], [Zip], [Phone] FROM [dbo].[Supplier] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Supplier' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
public string GetDatabaseVersion()
{
var mySQLString = "Select mdbVersion From tblCaptions";
if (GetConnection(out _myAccessConn)) return null;
try
{
using (var myAccessCommand = new OleDbCommand(mySQLString, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
_mdbVersion = dr.GetString(dr.GetOrdinal("mdbVersion"));
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
_myAccessConn.Close();
}
return _mdbVersion;
}
//public List<Models.MonitorFax> Get()
//public System.Web.Mvc.JsonResult Get()
public System.Web.Mvc.JsonResult Grid(KendoGridBinder.KendoGridRequest request)
{
Models.MonitorFax monitorFax = new Models.MonitorFax();
List<Models.MonitorFax> monitorFaxList = new List<Models.MonitorFax>();
try
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.WaldenFax, false))
{
using (var cm = ctx.Connection.CreateCommand())
{
cm.CommandText = "select SendID,Status,FaxName,"
+ " CreateTime,CompletionTime,PageCount,"
+ " RecipientName,Notes"
+ " from FaxesSendServer "
+ " where UserID = @UserID"
+ " and ShowFax = 'Y'"
+ " order by CreateTime DESC";
cm.CommandType = System.Data.CommandType.Text;
cm.Parameters.AddWithValue("@UserID",Environment.UserName);
using (var dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
monitorFax.SendID = int.Parse(dr["SendID"].ToString());
monitorFax.Status = dr["Status"].ToString();
//monitorFax.FaxName = dr["FaxName"].ToString();
monitorFax.CreateTime = dr["CreateTime"].ToString();
monitorFax.CompletionTime = dr["CompletionTime"].ToString();
monitorFax.PageCount = int.Parse(dr["PageCount"].ToString());
monitorFax.RecipientName = dr["RecipientName"].ToString();
monitorFax.Notes = dr["Notes"].ToString();
monitorFaxList.Add(monitorFax);
}
}
var grid = new KendoGridBinder.KendoGrid<Models.MonitorFax>(request,monitorFaxList);
// var data = new KendoGrid<Employee>(request, employees);
// return Json(data);
//using System.Web.Mvc;
//using ComicShop.Models;
//using System.Collections.Generic;
//System.Web.Mvc.Controller Json
//System.Web.Mvc.Controller.
//System.Collections.Generic.J
return Json(grid);
}
}
}
catch
{
return monitorFaxList;
}
}
/// <summary>
/// Retrieves data from the data base into a CSLA editable child business object of type <see cref="Cart"/>
/// using the criteria provided.
/// </summary>
/// <param name="criteria">Object of type <see cref="CartCriteria"/></param>
/// <returns></returns>
private void Child_Fetch(CartCriteria criteria)
{
bool cancel = false;
OnChildFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [CartId], [UniqueID], [ItemId], [Name], [Type], [Price], [CategoryId], [ProductId], [IsShoppingCart], [Quantity] FROM [dbo].[Cart] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Cart' using the following criteria: {0}.", criteria));
}
}
}
OnChildFetched();
}
/// <summary>
/// Retrieves data from the data base into a CSLA editable child business object of type <see cref="Account"/>
/// using the criteria provided.
/// </summary>
/// <param name="criteria">Object of type <see cref="AccountCriteria"/></param>
/// <returns></returns>
private void Child_Fetch(AccountCriteria criteria)
{
bool cancel = false;
OnChildFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [AccountId], [UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone] FROM [dbo].[Account] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Account' using the following criteria: {0}.", criteria));
}
}
}
OnChildFetched();
}
public static ArrayList GetTemplatesList(string _templateID)
{
ArrayList aTemplates = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT TemplateTextID,AccountID,"
+ " TemplateID,Text "
+ " FROM TemplateForChoice"
+ " where AccountID = " + Common.AccountID
+ " and TemplateID = " + _templateID
+ " order by Text";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
aTemplates.Add(dr["Text"] + "~"
+ dr["TemplateTextID"]);
// + dr.GetDateTime(1).ToShortDateString() + "~"
// + dr.GetString(2) + "~"
// + dr.GetString(3) + "~"
// + dr.GetInt32(4).ToString());
}
return aTemplates;
}
}
}
}
public static ArrayList GetTemplates(string _templateType)
{
ArrayList aTemplates = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select B.Name ,B.TemplateID from TemplateType A,Templates B"
+ " where A.Name = '" + _templateType + "'"
+ " and A.AccountID = " + Common.AccountID
+ " and B.AccountID = " + Common.AccountID
+ " and A.TemplateTypeID = B.TemplateTypeID"
+ " order by B.Name";
Common.Log(cm.CommandText);
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
aTemplates.Add(dr.GetString(0) + "~"
+ dr.GetInt32(1).ToString());
// + dr.GetDateTime(1).ToShortDateString() + "~"
// + dr.GetString(2) + "~"
// + dr.GetString(3) + "~"
// + dr.GetInt32(4).ToString());
}
return aTemplates;
}
}
}
}
protected void DataPortal_Fetch(SupplierCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("[dbo].[CSLA_Supplier_Select]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
command.Parameters.AddWithValue("@p_NameHasValue", criteria.NameHasValue);
command.Parameters.AddWithValue("@p_Addr1HasValue", criteria.Addr1HasValue);
command.Parameters.AddWithValue("@p_Addr2HasValue", criteria.Addr2HasValue);
command.Parameters.AddWithValue("@p_CityHasValue", criteria.CityHasValue);
command.Parameters.AddWithValue("@p_StateHasValue", criteria.StateHasValue);
command.Parameters.AddWithValue("@p_ZipHasValue", criteria.ZipHasValue);
command.Parameters.AddWithValue("@p_PhoneHasValue", criteria.PhoneHasValue);
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Supplier' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
private void DataPortal_Fetch(ProfileCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [UniqueID], [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate] FROM [dbo].[Profiles] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Profiles' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
private void DataPortal_Fetch(OrderCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [OrderId], [UserId], [OrderDate], [ShipAddr1], [ShipAddr2], [ShipCity], [ShipState], [ShipZip], [ShipCountry], [BillAddr1], [BillAddr2], [BillCity], [BillState], [BillZip], [BillCountry], [Courier], [TotalPrice], [BillToFirstName], [BillToLastName], [ShipToFirstName], [ShipToLastName], [AuthorizationNumber], [Locale] FROM [dbo].[Orders] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Orders' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
/// <summary>
/// Gets the user dictionary words.
/// </summary>
/// <returns>
/// The collection of words.
/// </returns>
public IList<string> GetUserWords()
{
const string CommandText = @"
SELECT [Word]
FROM
[dbo].[SpellCheckerUserWords]";
using (var connectionManager = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
{
using (var reader = cmd.ExecuteReader())
{
using (var safeReader = new SafeDataReader(reader))
{
var words = new List<string>();
while (safeReader.Read())
{
words.Add(safeReader.GetString("Word"));
}
return words;
}
}
}
}
}
private void DataPortal_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("[dbo].[CSLA_Category_Select]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
command.Parameters.AddWithValue("@p_NameHasValue", criteria.NameHasValue);
command.Parameters.AddWithValue("@p_DescnHasValue", criteria.DescriptionHasValue);
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
MarkOld();
}
protected void DataPortal_Fetch()
{
using (var manager = ConnectionManager <SqlConnection> .GetManager(TestDBConnection, true))
{
using (var command = new SqlCommand("Select * From Table2", manager.Connection))
{
using (var reader = new Csla.Data.SafeDataReader(command.ExecuteReader()))
{
while (reader.Read())
{
Add(TransactionContextUser.GetTransactionContextUser(reader));
}
}
}
}
}
public static SortingProcessList GetSortingProcessList(string sortingdate, string taskno, string picklinecode)
{
SortingProcessList sortingProcessList = new SortingProcessList();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm1 = cn.CreateCommand())
{
cm1.CommandText =
"SELECT * FROM t_sortline_process WHERE " +
"(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" +
"(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" +
"(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))";
cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno);
cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate);
cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode);
using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader()))
{
while (dr.Read())
{
SortingProcessInfo sortingProcess = new SortingProcessInfo();
sortingProcess.SORTINGTASKNO = dr.GetString("SORTINGTASKNO");
sortingProcess.ORDERDATE = dr.GetString("ORDERDATE");
sortingProcess.PICKLINECODE = dr.GetString("PICKLINECODE");
sortingProcess.PICKLINENAME = dr.GetString("PICKLINENAME");
sortingProcess.QTY_PRODCUT_TOT = dr.GetInt32("QTY_PRODCUT_TOT");
sortingProcess.QTY_ROUTE_TOT = dr.GetInt32("QTY_ROUTE_TOT");
sortingProcess.QTY_CUSTOMER_TOT = dr.GetInt32("QTY_CUSTOMER_TOT");
sortingProcess.QTY_PRODUCT = dr.GetInt32("QTY_PRODUCT");
sortingProcess.QTY_ROUTE = dr.GetInt32("QTY_ROUTE");
sortingProcess.QTY_CUSTOMER = dr.GetInt32("QTY_CUSTOMER");
sortingProcess.CUSTOMER_CODE = dr.GetString("CUSTOMER_CODE");
sortingProcess.CUSTOMER_DESC = dr.GetString("CUSTOMER_DESC");
sortingProcess.ROUTE_CODE = dr.GetString("ROUTE_CODE");
sortingProcess.ROUTE_NAME = dr.GetString("ROUTE_NAME");
sortingProcess.RECEIVE_TIME = dr.GetDateTime("RECEIVE_TIME");
sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY");
sortingProcessList.Add(sortingProcess);
}
}
}
}
return(sortingProcessList);
}
public static Dictionary <string, string> GetSortingProcessInfo(string sortingdate, string taskno, string picklinecode)
{
Dictionary <string, string> sortingDictionary = new Dictionary <string, string>();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm1 = cn.CreateCommand())
{
cm1.CommandText =
"SELECT * FROM t_sortline_process WHERE " +
"(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" +
"(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" +
"(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))";
cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno);
cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate);
cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode);
using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader()))
{
if (dr.Read())
{
sortingDictionary.Add("SORTINGTASKNO", dr.GetString("SORTINGTASKNO"));
sortingDictionary.Add("ORDERDATE ", dr.GetString("ORDERDATE"));
sortingDictionary.Add("PICKLINECODE", dr.GetString("PICKLINECODE"));
sortingDictionary.Add("PICKLINENAME", dr.GetString("PICKLINENAME"));
sortingDictionary.Add("QTY_PRODCUT_TOT", dr.GetInt32("QTY_PRODCUT_TOT").ToString());
sortingDictionary.Add("QTY_ROUTE_TOT", dr.GetInt32("QTY_ROUTE_TOT").ToString());
sortingDictionary.Add("QTY_CUSTOMER_TOT", dr.GetInt32("QTY_CUSTOMER_TOT").ToString());
sortingDictionary.Add("QTY_PRODUCT", dr.GetInt32("QTY_PRODUCT").ToString());
sortingDictionary.Add("QTY_ROUTE", dr.GetInt32("QTY_ROUTE").ToString());
sortingDictionary.Add("QTY_CUSTOMER", dr.GetInt32("QTY_CUSTOMER").ToString());
sortingDictionary.Add("CUSTOMER_CODE", dr.GetString("CUSTOMER_CODE") != "" ? dr.GetString("CUSTOMER_CODE"):"无");
sortingDictionary.Add("CUSTOMER_DESC", dr.GetString("CUSTOMER_DESC") != "" ? dr.GetString("CUSTOMER_DESC"):"无");
sortingDictionary.Add("ROUTE_CODE", dr.GetString("ROUTE_CODE") != "" ? dr.GetString("ROUTE_CODE") : "无");
sortingDictionary.Add("ROUTE_NAME", dr.GetString("ROUTE_NAME") != "" ? dr.GetString("ROUTE_NAME") : "无");
sortingDictionary.Add("RECEIVE_TIME", dr.GetDateTime("RECEIVE_TIME").ToString());
sortingDictionary.Add("EFFICIENCY", dr.GetDouble("EFFICIENCY").ToString("#0.00") + "条/小时");
sortingDictionary.Add("Progress", dr.GetDouble("Progress").ToString());
}
}
}
}
return(sortingDictionary);
}
public static void GetSortingProcessList(object o)
{
SortingProcessList sortingProcessList = new SortingProcessList();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
MySqlTransaction tran = cn.BeginTransaction();
using (var cm = cn.CreateCommand())
{
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "P_SORTLINE_Efficiency";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
SortingProcessInfo sortingProcess = new SortingProcessInfo();
sortingProcess.SORTINGTASKNO = dr.GetString("SORTINGTASKNO");
sortingProcess.ORDERDATE = dr.GetString("ORDERDATE");
sortingProcess.PICKLINECODE = dr.GetString("PICKLINECODE");
sortingProcess.PICKLINENAME = dr.GetString("PICKLINENAME");
sortingProcess.QTY_PRODCUT_TOT = dr.GetInt32("QTY_PRODCUT_TOT");
sortingProcess.QTY_ROUTE_TOT = dr.GetInt32("QTY_ROUTE_TOT");
sortingProcess.QTY_CUSTOMER_TOT = dr.GetInt32("QTY_CUSTOMER_TOT");
sortingProcess.QTY_PRODUCT = dr.GetInt32("QTY_PRODUCT");
sortingProcess.QTY_ROUTE = dr.GetInt32("QTY_ROUTE");
sortingProcess.QTY_CUSTOMER = dr.GetInt32("QTY_CUSTOMER");
sortingProcess.CUSTOMER_CODE = dr.GetString("CUSTOMER_CODE");
sortingProcess.CUSTOMER_DESC = dr.GetString("CUSTOMER_DESC");
sortingProcess.ROUTE_CODE = dr.GetString("ROUTE_CODE");
sortingProcess.ROUTE_NAME = dr.GetString("ROUTE_NAME");
sortingProcess.RECEIVE_TIME = DateTime.Now;
sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY");
sortingProcess.Progress = dr.GetDouble("Progress");
sortingProcessList.Add(sortingProcess);
}
}
}
try
{
foreach (SortingProcessInfo sortingProcessInfo in sortingProcessList)
{
if (!sortingProcessInfo.IsExist())
{
using (var cm = cn.CreateCommand())
{
cm.Transaction = tran;
cm.CommandType = CommandType.Text;
StringBuilder SQL = new StringBuilder();
SQL.Append("INSERT ");
SQL.Append(" INTO t_sortline_process ");
SQL.Append(" ( ");
SQL.Append(
" ID,SORTINGTASKNO,ORDERDATE,PICKLINECODE,PICKLINENAME,QTY_PRODCUT_TOT,QTY_ROUTE_TOT,QTY_CUSTOMER_TOT,QTY_PRODUCT,QTY_ROUTE,QTY_CUSTOMER,CUSTOMER_CODE,CUSTOMER_DESC,ROUTE_CODE,ROUTE_NAME,EFFICIENCY,RECEIVE_TIME,Progress ");
SQL.Append(" ) ");
SQL.Append(" VALUES ");
SQL.Append(" ( ");
SQL.Append(
" @ID,@SORTINGTASKNO,@SORT_DATE,@SORTLINE_CODE,@SORTLINE_DESC,@QTY_PRODCUT_TOT,@QTY_ROUTE_TOT,@QTY_CUSTOMER_TOT,@QTY_PRODUCT,@QTY_ROUTE,@QTY_CUSTOMER,@CUSTOMER_CODE,@CUSTOMER_DESC,@ROUTE_CODE,@ROUTE_NAME,@EFFICIENCY,@RECEIVE_TIME,@Progress ");
SQL.Append(" )");
cm.CommandText = SQL.ToString();
cm.Parameters.AddWithValue("@ID", Guid.NewGuid().ToString());
cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO);
cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE);
cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE);
cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME);
cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT);
cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT);
cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT);
cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT);
cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE);
cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER);
cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE);
cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC);
cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE);
cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME);
cm.Parameters.AddWithValue("@EFFICIENCY", sortingProcessInfo.EFFICIENCY);
cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME);
cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress);
cm.ExecuteNonQuery();
}
}
else
{
using (var cm = cn.CreateCommand())
{
cm.Transaction = tran;
cm.CommandType = CommandType.Text;
StringBuilder SQL = new StringBuilder();
if (sortingProcessInfo.PICKLINECODE == o.ToString())
{
SQL.Append("UPDATE t_sortline_process ");
SQL.Append(
" SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, ");
SQL.Append(
" ROUTE_NAME = @ROUTE_NAME,RECEIVE_TIME = @RECEIVE_TIME,Progress=@Progress");
SQL.Append(
" WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE");
}
else
{
SQL.Append("UPDATE t_sortline_process ");
SQL.Append(
" SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, ");
SQL.Append(
" ROUTE_NAME = @ROUTE_NAME,Progress=@Progress ");
SQL.Append(" WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE");
}
cm.CommandText = SQL.ToString();
cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO);
cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE);
cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE);
cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME);
cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT);
cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT);
cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT);
cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT);
cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE);
cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER);
cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE);
cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC);
cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE);
cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME);
cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress);
if (sortingProcessInfo.PICKLINECODE == o.ToString())
{
cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME);
}
cm.ExecuteNonQuery();
}
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
//return sortingProcessList;
}
public static ArrayList GetPatientChartFilterList()
{
ArrayList _filterList = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select Name, Description,FilterID"
+ " from PatientChartFilter"
+ " where AccountID =" + Common.AccountID
+ " and FilterId > 0"
+ " order by Description";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
_filterList.Add(dr.GetString(0) + "~"
+ dr.GetString(1) + "~"
+ dr.GetInt32(2));
}
return _filterList;
}
}
}
}