/// <summary>
/// 获得分厂ID和名字
/// </summary>
/// <returns></returns>
public string GetFactory()
{
ComplexQuery query = new ComplexQuery();
query.AddNeedField("Factory", "ID");
query.AddNeedField("Factory", "Name");
DataTable dt = dataFactory.Query(query);
if (dt.Rows.Count == 0)
{
throw new Exception("查无数据");
}
return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name"));
}
public string GetEletricalRoom(int factoryId)
{
try
{
string connString = ConnectionStringFactory.GetConnectionStringByFactoryID(factoryId, DatabaseType.EnergyDatabase);
ISqlServerDataFactory factory = new SqlServerDataFactory(connString);
ComplexQuery query = new ComplexQuery();
query.AddNeedField("ElectricalRoom", "ID");
query.AddNeedField("ElectricalRoom", "Name");
DataTable dt = factory.Query(query);
return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name"));
}
catch
{
return("[]");
}
}
public string GetReportNameBy(int id)
{
ComplexQuery cmquery = new ComplexQuery();
cmquery.AddNeedField("Report", "Name");
cmquery.AddCriterion("ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(cmquery);
return(table.Rows[0][0].ToString().Trim());
}
public string GetUserNameById(string id)
{
ComplexQuery cmquery = new ComplexQuery();
cmquery.AddNeedField("users", "USER_NAME");
cmquery.AddCriterion("USER_ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(cmquery);
return(table.Rows[0][0].ToString().Trim());
}
/// <summary>
/// 获得视图中所有变量的Id值
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
private ArrayList GetParametorsId(string viewName)
{
ArrayList result = new ArrayList();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField("ContrastTable", "VariableName");
cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal);
DataTable data = dataFactory.Query(cmpquery);
foreach (DataRow row in data.Rows)
{
result.Add(row["VariableName"].ToString().Trim());
}
return(result);
}
/// <summary>
/// 获得视图中所有变量的Id值
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
public System.Collections.ArrayList GetVariableId(int productLineId, string viewName)
{
string connectionString = ConnectionStringFactory.GetConnectionStringByProductLineID(productLineId, DatabaseType.DCSSystemDatabase);
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
ArrayList result = new ArrayList();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField("ContrastTable", "VariableName");
cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal);
DataTable data = dataFactory.Query(cmpquery);
foreach (DataRow row in data.Rows)
{
result.Add(row["VariableName"].ToString().Trim());
}
return(result);
}
/// <summary>
/// 获得实时数据的table表
/// </summary>
/// <param name="dataSetInformations"></param>
/// <returns></returns>
private DataTable GetDataItemTable(IEnumerable <DataSetInformation> dataSetInformations)
{
//DataItem result = new DataItem();
ComplexQuery cmpquery = new ComplexQuery();
foreach (var item in dataSetInformations)
{
cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId);
}
cmpquery.JoinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
cmpquery.TopNumber = 1;
//cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true);
DataTable table = dataFactory.Query(cmpquery);
return(table);
}
private DataItem GetDataItem(DataSetInformation dataSetInformation)
{
DataItem result = new DataItem();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField(dataSetInformation.TableName, dataSetInformation.FieldName, dataSetInformation.FieldName);
cmpquery.TopNumber = 1;
cmpquery.AddOrderByClause("v_date", true);
DataTable table = dataFactory.Query(cmpquery);
foreach (DataRow item in table.Rows)
{
result = new DataItem
{
ID = dataSetInformation.ViewId,
Value = item[dataSetInformation.FieldName].ToString().Trim()
};
}
return(result);
}
/// <summary>
/// 获得实时数据的table表
/// </summary>
/// <param name="dataPathInfor"></param>
/// <returns></returns>
public DataTable GetDataItemTable(int productLineId, IEnumerable <DataPathInformation> dataPathInfor)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
ComplexQuery cmpquery = new ComplexQuery();
foreach (var item in dataPathInfor)
{
cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId);
}
cmpquery.JoinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
cmpquery.TopNumber = 1;
//cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true);
DataTable table = dataFactory.Query(cmpquery);
return(table);
}
static void Main(string[] args)
{
string conn = "Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True";
SqlServerDataFactory dataFactory = new SqlServerDataFactory(conn);
DataTable dttest = new DataTable();
DataColumn dc = new DataColumn("d", typeof(string));
dttest.Columns.Add(dc);
DataColumn dc1 = new DataColumn("c", typeof(int));
dttest.Columns.Add(dc1);
DataRow dr = dttest.NewRow();
dr["c"] = 2;
dr["d"] = "123ss";
dttest.Rows.Add(dr);
DataRow dr1 = dttest.NewRow();
dr1["c"] = 3;
dr1["d"] = "123ss";
dttest.Rows.Add(dr1);
//dataFactory.Insert("B", dttest);
string[] key = { "c" };
dataFactory.Update("B", dttest, key);
Console.ReadKey();
//////////////////////////////////////delete
Delete delete = new Delete("tableName");
delete.AddCriterions("a", "zhangxin", "1", CriteriaOperator.MoreThan);
delete.AddCriterions("a", "zhangdi", 2, CriteriaOperator.LessThan);
delete.AddCriterions("b", "2", CriteriaOperator.Equal);
delete.AddSqlOperator(SqlOperator.OR);
SqlCommand deleteCmd = new SqlCommand();
DeleteTranslator.TranslateIntoDelete(delete, deleteCmd);
Console.WriteLine(deleteCmd.CommandText);
Console.WriteLine(deleteCmd.Parameters.Count);
//Console.ReadKey();
//////////////////////////////////////////////////
Test test = new Test
{
a = "1",
b = "2",
c = "3"
};
Insert <Test> insert = new Insert <Test>("tableName", test);
insert.AddExcludeField("a");
SqlCommand insertCmd = new SqlCommand();
SaveTranslator.TranslateIntoInsert <Test>(insert, insertCmd);
Console.WriteLine(insertCmd.CommandText);
Console.WriteLine(insertCmd.Parameters.Count);
//Console.ReadKey();
////////////////////////////////////////////////////////////
Test test1 = new Test
{
a = "1",
b = "2",
c = "3"
};
Update <Test> update = new Update <Test>("taleName", test1);
update.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
update.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
update.AddExcludeField("c");
update.AddSqlOperator(SqlOperator.AND);
SqlCommand updateCmd = new SqlCommand();
SaveTranslator.TranslateIntoUpdate <Test>(update, updateCmd);
Console.WriteLine(updateCmd.CommandText);
Console.WriteLine(updateCmd.Parameters.Count);
//Console.ReadKey();
////////////////////////////////////////////////////////////
Query query = new Query("tableName");
query.AddCriterion("a.b", "1", CriteriaOperator.Equal);
//query.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
query.AddCriterion("c", "____", CriteriaOperator.Like);
query.AddOrderByClause(new OrderByClause("a", false));
query.AddOrderByClause(new OrderByClause("b", true));
//query.AddSqlOperator(SqlOperator.AND);
SqlCommand queryCmd = new SqlCommand();
QueryTranslator.TranslateIntoSelect(query, queryCmd);
Console.WriteLine(queryCmd.CommandText);
Console.WriteLine(queryCmd.Parameters.Count);
//Console.ReadKey();
//////////////////////////////////////////////////////////////
ComplexQuery cmquery = new ComplexQuery();
IList <NeedField> list = new List <NeedField>();
NeedField needField = new NeedField
{
TableName = "tableName1",
FieldName = "a",
VariableName = ""
};
list.Add(needField);
cmquery.NeedFields = list;
cmquery.AddNeedField("tableName2", "b");
cmquery.AddNeedField("tableName3", "c", "test");
IDictionary <string, string> testjoin = new Dictionary <string, string>();
testjoin.Add("tableName1", "c1");
testjoin.Add("tableName2", "c2");
testjoin.Add("tableName3", "c3");
//cmquery.AddJoinCriterion("assss", JoinType.FULL_JOIN);
cmquery.AddJoinCriterion(testjoin, JoinType.INNER_JOIN);
//cmquery.IsDictinct = true;
//cmquery.TopNumber = 9;
cmquery.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
cmquery.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
cmquery.AddCriterion("c", "c", "3", CriteriaOperator.Like);
List <OrderByClause> orders = new List <OrderByClause>();
orders.Add(new OrderByClause("a", true));
orders.Add(new OrderByClause("b", false));
cmquery.OrderByClauses = orders;
SqlCommand cmqueryCmd = new SqlCommand();
ComplexQueryTranslator.TranslateIntoComplexQuery(cmquery, cmqueryCmd);
Console.WriteLine(cmqueryCmd.CommandText);
Console.WriteLine(cmqueryCmd.Parameters.Count);
//Console.ReadKey();
///////////////////////////////////////////////////////////
ComplexQuery testquery = new ComplexQuery();
Console.WriteLine(testquery.TopNumber);
Console.WriteLine(DateTime.Today);
Console.WriteLine(DateTime.Now.AddDays(-1).Date);
//Console.ReadKey();
///////////////////////////////////////////////////////
//Query testQuery = new Query("B");
//SqlServerDataFactory dataFactory = new SqlServerDataFactory("Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True");
//IEnumerable<BModle> result = dataFactory.Query<BModle>(testQuery);
//foreach (var item in result)
//{
// //BModle a = (BModle)item;
// Console.WriteLine(item.d);
// Console.WriteLine(item.c);
//}
//Console.WriteLine(result.Count());
//Console.ReadKey();
JoinCriterion joinCri = new JoinCriterion();
Console.WriteLine("++" + joinCri.DefaultJoinFieldName + "++");
Console.ReadKey();
Criterion testcri = new Criterion("name", "", 1, CriteriaOperator.Equal);
Console.WriteLine(testcri.ParameterName);
Console.ReadKey();
//SqlServerDataFactory dataFactory = new SqlServerDataFactory("");
//SqlParameter a = new SqlParameter();
//SqlParameter b = new SqlParameter("", typeof(int));
//dataFactory.Query("", a, b);
//dataFactory.Query("");
//SqlParameter[] arry = { new SqlParameter(), new SqlParameter("", typeof(int)) };
//dataFactory.Query("", arry);
}