public static SortingLineList GetSortingLines(bool isaddnull)
{
SortingLineList sortingLineList = new SortingLineList();
if (isaddnull)
{
sortingLineList.Add(new SortingLine());
}
using (var cn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "select * from T_SORTINGLINE order by Linecode";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
SortingLine sortingLine = new SortingLine();
sortingLine.ID = dr.GetString("LineCode");
sortingLine.Name = dr.GetString("LineName");
sortingLine.Type = dr.GetString("LineType");
sortingLineList.Add(sortingLine);
}
}
}
}
return(sortingLineList);
}
public static TaskInfoList GetServerTaskBatchs(string orderdate, bool isaddnull)
{
TaskInfoList taskBatchList = new TaskInfoList();
if (isaddnull)
{
taskBatchList.Add(new TaskInfo());
}
using (var cn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "select * from tsp_plan tsp join t_sortingline s on tsp.sortinglines = s.id WHERE tsp.orderDate = '" + orderdate + "'";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
TaskInfo taskInfo = new TaskInfo();
taskInfo.TaskNo = dr.GetString("taskno");
taskInfo.LineName = dr.GetString("linename");
taskInfo.LineCode = dr.GetString("linecode");
taskInfo.LineType = dr.GetString("linetype");
taskBatchList.Add(taskInfo);
}
}
}
}
return(taskBatchList);
}
private void DataFetch(Criteria criteria)
{
using (SqlConnection con = new SqlConnection(DatabaseManager.GetConnectionString("WWConString")))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spWWAuthenticateUser";
cmd.CommandTimeout = DatabaseManager.SqlCommandTimeout();
cmd.Parameters.AddWithValue("@username", criteria.CriteriaInfo.Username);
cmd.Parameters.AddWithValue("@password", criteria.CriteriaInfo.Password);
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cmd.ExecuteReader()))
{
while (dr.Read())
{
this.Add(Authenticate.GetData(dr));
}
}
}
}
}
private void Fetch(Csla.Data.SafeDataReader data)
{
Id = data.GetInt32("Id");
Name = string.Format("{0} {1}",
data.GetString("FirstName"),
data.GetString("LastName"));
}
public static TaskBatchList GetTaskBatchs(string orderdate, bool isaddnull)
{
TaskBatchList taskBatchList = new TaskBatchList();
if (isaddnull)
{
taskBatchList.Add(new TaskBatch());
}
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "SELECT DISTINCT(sortingtaskno) FROM t_sorting_line_task_history tsp WHERE tsp.orderDate = '" + orderdate + "'";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
TaskBatch taskBatch = new TaskBatch();
taskBatch.ID = dr.GetString("sortingtaskno");
taskBatch.Name = dr.GetString("sortingtaskno");
taskBatchList.Add(taskBatch);
}
}
}
}
return(taskBatchList);
}
public void TestSafeDataReader()
{
List <string> list = new List <string>();
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT Name, Date, Age FROM Table1";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read()) //returns two results
{
string output = dr.GetString("Name") + ", age " + dr.GetInt32("Age") + ", added on " + dr.GetSmartDate("Date");
Assert.AreEqual("varchar", dr.GetDataTypeName("Name"));
Assert.AreEqual(false, dr.IsClosed);
list.Add(output);
Console.WriteLine(output);
}
dr.Close();
Assert.AreEqual(true, dr.IsClosed);
}
cn.Close();
}
Assert.AreEqual("Bill, age 56, added on 12/23/2004", list[0]);
Assert.AreEqual("Jim, age 33, added on 1/14/2003", list[1]);
}
private void Fetch(Csla.Data.SafeDataReader dr)
{
IdTest = dr.GetInt32(""); // inside "" is from sp column returned
TestName = dr.GetString("");
//Mark this data as persisted in the data store
MarkOld();
}
private void Fetch(Csla.Data.SafeDataReader data)
{
using (BypassPropertyChecks)
{
LineItemId = data.GetInt32("LineItemId");
PersonId = data.GetInt32("PersonId");
}
}
private void Fetch(Csla.Data.SafeDataReader data)
{
using (BypassPropertyChecks)
{
Id = data.GetInt32("Id");
Name = data.GetString("Name");
}
}
private void Fetch(Csla.Data.SafeDataReader dr)
{
//IdTest = dr.GetInt32(""); // inside "" is from sp column returned
Fullname = dr.GetString("Fullname");
//Mark this data as persisted in the data store
MarkOld();
UserProfile.SetProperties("", "", Fullname, "");
}
private void Child_Fetch(Csla.Data.SafeDataReader dataReader)
{
using (BypassPropertyChecks)
{
Id = dataReader.GetString("ID");
Name = dataReader.GetString("Name");
Value = dataReader.GetString("Value");
Description = dataReader.GetString("Description");
}
}
private void Fetch(Csla.Data.SafeDataReader data)
{
using (BypassPropertyChecks)
{
Id = data.GetInt32("Id");
if (!data.IsDBNull("ShipDate"))
{
ShipDate = data.GetDateTime("ShipDate");
}
Persons = DataPortal.FetchChild <OrderLinePersons>(Id);
}
}
private void Fetch(Csla.Data.SafeDataReader dr)
{
//IdTest = dr.GetInt32(""); // inside "" is from sp column returned
Username = dr.GetString("Username");
Password = dr.GetString("Password");
Fullname = dr.GetString("Fullname");
GroupRights = dr.GetString("GroupRights");
//Mark this data as persisted in the data store
MarkOld();
UserProfile.SetProperties(Username, Password, Fullname, GroupRights);
}
private void Child_Fetch(Csla.Data.SafeDataReader dataReader)
{
using (BypassPropertyChecks)
{
Id = dataReader.GetString("Id");
PrintLable = dataReader.GetString("PrintLable");
PrintText = dataReader.GetString("PrintText");
X = dataReader.GetInt32("x");
Y = dataReader.GetInt32("y");
Font = dataReader.GetString("Font");
FontSize = dataReader.GetFloat("FontSize");
}
}
public void ThrowSqlException()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM NonExistantTable";
Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader());
}
}
public void ThrowSqlException()
{
// TODO: Connection strings were lost, and I don't know how to set them correctly
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM NonExistantTable";
Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader());
}
}
public void GetDataTypes()
{
// TODO: Connection strings were lost, and I don't know how to set them correctly
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
cm.CommandText =
"SELECT BITFIELD, CHARFIELD, DATETIMEFIELD, UNIQUEIDENTIFIERFIELD, SMALLINTFIELD, INTFIELD, BIGINTFIELD, TEXT FROM MultiDataTypes";
bool bitfield;
char charfield;
Csla.SmartDate datetimefield;
Guid uniqueidentifierfield;
System.Int16 smallintfield;
System.Int32 intfield;
System.Int64 bigintfield;
System.String text;
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dr.Read();
bitfield = dr.GetBoolean("BITFIELD");
//this causes an error in vb version (char array initialized to nothing in vb version
//and it's initialized with new Char[1] in c# version)
charfield = dr.GetChar("CHARFIELD");
datetimefield = dr.GetSmartDate("DATETIMEFIELD");
uniqueidentifierfield = dr.GetGuid("UNIQUEIDENTIFIERFIELD");
smallintfield = dr.GetInt16("SMALLINTFIELD");
intfield = dr.GetInt32("INTFIELD");
bigintfield = dr.GetInt64("BIGINTFIELD");
text = dr.GetString("TEXT");
dr.Close();
}
}
cn.Close();
Assert.AreEqual(false, bitfield);
Assert.AreEqual('z', charfield);
Assert.AreEqual("12/13/2005", datetimefield.ToString());
Assert.AreEqual("c0f92820-61b5-11da-8cd6-0800200c9a66", uniqueidentifierfield.ToString());
Assert.AreEqual(32767, smallintfield);
Assert.AreEqual(2147483647, intfield);
Assert.AreEqual(92233720368547111, bigintfield);
Assert.AreEqual("a bunch of text...a bunch of text...a bunch of text...a bunch of text...", text);
}
public void CloseSafeDataReader()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM Table2";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
Assert.AreEqual(false, dr.IsClosed);
dr.Close();
Assert.AreEqual(true, dr.IsClosed);
}
}
}
public void CloseSafeDataReader()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM Table2";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
Assert.AreEqual(false, dr.IsClosed);
dr.Close();
Assert.AreEqual(true, dr.IsClosed);
}
}
}
public void TestFieldCount()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName, LastName FROM Table2";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
Assert.IsTrue(dr.FieldCount > 0);
Assert.AreEqual(false, dr.NextResult());
dr.Close();
}
cn.Close();
}
}
public void CloseSafeDataReader()
{
// TODO: Connection strings were lost, and I don't know how to set them correctly
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM Table2";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
Assert.AreEqual(false, dr.IsClosed);
dr.Close();
Assert.AreEqual(true, dr.IsClosed);
}
}
}
/// <summary>
/// 根据行字体的大小动态获取每行的位置
/// </summary>
public static PrintRows GetPrintRows()
{
PrintRows printRows = new PrintRows();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT c.y+1 row,SUM(c.maxfontsize) y ");
SQL.Append("FROM ( ");
SQL.Append(" (SELECT 0 y,0 maxfontsize ");
SQL.Append(" ) ");
SQL.Append("UNION ALL ");
SQL.Append(" (SELECT a.y,b.maxfontsize ");
SQL.Append(" FROM ");
SQL.Append(" (SELECT y,MAX(fontsize) maxfontsize ");
SQL.Append(" FROM t_printsetting ");
SQL.Append(" GROUP BY y ");
SQL.Append(" ORDER BY y ");
SQL.Append(" )a ");
SQL.Append(" JOIN ");
SQL.Append(" (SELECT y,MAX(fontsize) + 8 maxfontsize ");
SQL.Append(" FROM t_printsetting ");
SQL.Append(" GROUP BY y ");
SQL.Append(" ORDER BY y ");
SQL.Append(" )b ON a.y >= b.y "); //将小于当前行的每行高度关联出来
SQL.Append(" )) c ");
SQL.Append("GROUP BY c.y"); //合计当前行以上的所有高度得到当前行的相对位置
cm.CommandText = SQL.ToString();
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
printRows.Add(dr.GetInt32("row"), dr.GetFloat("y"));
}
}
}
}
return(printRows);
}
public void IsDBNull()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
cm.CommandText = "SELECT TEXT, BIGINTFIELD, IMAGEFIELD FROM MultiDataTypes";
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dr.Read();
Assert.AreEqual(true, dr.IsDBNull(2));
Assert.AreEqual(false, dr.IsDBNull(1));
dr.Close();
}
}
cn.Close();
}
private void DataPortal_Fetch()
{
RaiseListChangedEvents = false;
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "select * from t_SortingTaskArrive";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
Add(SortingTaskArrive.GetSortingTaskArrive("0"));
}
}
}
}
RaiseListChangedEvents = true;
}
public void GetSchemaTable()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
DataTable dtSchema = null;
cm.CommandText = "SELECT * FROM MultiDataTypes";
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dtSchema = dr.GetSchemaTable();
dr.Close();
}
}
cn.Close();
Assert.AreEqual("BIGINTFIELD", dtSchema.Rows[0][0]);
Assert.AreEqual(typeof(System.Int64), dtSchema.Rows[0][12]);
Assert.AreEqual(typeof(System.Byte[]), dtSchema.Rows[1][12]);
//Console.WriteLine();
//foreach (DataColumn c in dtSchema.Columns)
//{
// Console.WriteLine(c.ColumnName);
//}
//Console.WriteLine();
//for (int i = 0; i < dtSchema.Rows.Count; i++)
//{
// Console.WriteLine(dtSchema.Rows[i][0]);
//}
//Console.WriteLine();
//for (int i = 0; i < dtSchema.Rows.Count; i++)
//{
// Console.WriteLine(dtSchema.Rows[i][12]);
//}
}
private void DataPortal_Fetch()
{
RaiseListChangedEvents = false;
IsReadOnly = false;
using (SqlConnection connection = new SqlConnection(DataConnection.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("GetCompanies", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
using (Csla.Data.SafeDataReader reader = new Csla.Data.SafeDataReader(command.ExecuteReader()))
{
while (reader.Read())
{
Add(new NameValueListBase <int, string> .NameValuePair(reader.GetInt32("CompanyId"), reader.GetString("CompanyName")));
}
}
}
connection.Close();
}
IsReadOnly = true;
RaiseListChangedEvents = true;
}
public void GetSchemaTable()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
DataTable dtSchema = null;
cm.CommandText = "SELECT * FROM MultiDataTypes";
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dtSchema = dr.GetSchemaTable();
dr.Close();
}
}
cn.Close();
Assert.AreEqual("BIGINTFIELD", dtSchema.Rows[0][0]);
Assert.AreEqual(typeof(System.Int64), dtSchema.Rows[0][12]);
Assert.AreEqual(typeof(System.Byte[]), dtSchema.Rows[1][12]);
}
public void TestFieldCount()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName, LastName FROM Table2";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
Assert.IsTrue(dr.FieldCount > 0);
Assert.AreEqual(false, dr.NextResult());
dr.Close();
}
cn.Close();
}
}
public void TestSafeDataReader()
{
List<string> list = new List<string>();
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT Name, Date, Age FROM Table1";
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read()) //returns two results
{
string output = dr.GetString("Name") + ", age " + dr.GetInt32("Age") + ", added on " + dr.GetSmartDate("Date");
Assert.AreEqual("varchar", dr.GetDataTypeName("Name"));
Assert.AreEqual(false, dr.IsClosed);
list.Add(output);
Console.WriteLine(output);
}
dr.Close();
Assert.AreEqual(true, dr.IsClosed);
}
cn.Close();
}
Assert.AreEqual("Bill, age 56, added on 12/23/2004", list[0]);
Assert.AreEqual("Jim, age 33, added on 1/14/2003", list[1]);
}
static internal Authenticate GetData(Csla.Data.SafeDataReader dr)
{
return(new Authenticate(dr));
}
public void GetDataTypes()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
cm.CommandText =
"SELECT BITFIELD, CHARFIELD, DATETIMEFIELD, UNIQUEIDENTIFIERFIELD, SMALLINTFIELD, INTFIELD, BIGINTFIELD, TEXT FROM MultiDataTypes";
bool bitfield;
char charfield;
Csla.SmartDate datetimefield;
Guid uniqueidentifierfield;
System.Int16 smallintfield;
System.Int32 intfield;
System.Int64 bigintfield;
System.String text;
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dr.Read();
bitfield = dr.GetBoolean("BITFIELD");
//this causes an error in vb version (char array initialized to nothing in vb version
//and it's initialized with new Char[1] in c# version)
charfield = dr.GetChar("CHARFIELD");
datetimefield = dr.GetSmartDate("DATETIMEFIELD");
uniqueidentifierfield = dr.GetGuid("UNIQUEIDENTIFIERFIELD");
smallintfield = dr.GetInt16("SMALLINTFIELD");
intfield = dr.GetInt32("INTFIELD");
bigintfield = dr.GetInt64("BIGINTFIELD");
text = dr.GetString("TEXT");
dr.Close();
}
}
cn.Close();
Assert.AreEqual(false, bitfield);
Assert.AreEqual('z', charfield);
Assert.AreEqual("12/13/2005", datetimefield.ToString());
Assert.AreEqual("c0f92820-61b5-11da-8cd6-0800200c9a66", uniqueidentifierfield.ToString());
Assert.AreEqual(32767, smallintfield);
Assert.AreEqual(2147483647, intfield);
Assert.AreEqual(92233720368547111, bigintfield);
Assert.AreEqual("a bunch of text...a bunch of text...a bunch of text...a bunch of text...", text);
}
public void ThrowSqlException()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT FirstName FROM NonExistantTable";
Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader());
}
}
public void IsDBNull()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
cm.CommandText = "SELECT TEXT, BIGINTFIELD, IMAGEFIELD FROM MultiDataTypes";
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dr.Read();
Assert.AreEqual(true, dr.IsDBNull(2));
Assert.AreEqual(false, dr.IsDBNull(1));
dr.Close();
}
}
cn.Close();
}
public void GetSchemaTable()
{
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
SqlCommand cm = cn.CreateCommand();
DataTable dtSchema = null;
cm.CommandText = "SELECT * FROM MultiDataTypes";
cn.Open();
using (cm)
{
using (Csla.Data.SafeDataReader dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
dtSchema = dr.GetSchemaTable();
dr.Close();
}
}
cn.Close();
Assert.AreEqual("BIGINTFIELD", dtSchema.Rows[0][0]);
Assert.AreEqual(typeof(System.Int64), dtSchema.Rows[0][12]);
Assert.AreEqual(typeof(System.Byte[]), dtSchema.Rows[1][12]);
//Console.WriteLine();
//foreach (DataColumn c in dtSchema.Columns)
//{
// Console.WriteLine(c.ColumnName);
//}
//Console.WriteLine();
//for (int i = 0; i < dtSchema.Rows.Count; i++)
//{
// Console.WriteLine(dtSchema.Rows[i][0]);
//}
//Console.WriteLine();
//for (int i = 0; i < dtSchema.Rows.Count; i++)
//{
// Console.WriteLine(dtSchema.Rows[i][12]);
//}
}
internal static Setting GetSetting(Csla.Data.SafeDataReader dataReader)
{
return(DataPortal.FetchChild <Setting>(dataReader));
}
private TestTemplate(Csla.Data.SafeDataReader dr)
{
Fetch(dr);
}
static internal TestTemplate GetData(Csla.Data.SafeDataReader dr)
{
return(new TestTemplate(dr));
}
