private static DBPage ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBPage dbPage = new DBPage();
dbPage.ID = result.GetInt32(0);
dbPage.IDFichier = result.GetInt32(1);
dbPage.Ordre = result.GetInt32(2);
if (!string.IsNullOrEmpty(result.GetValue(3).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(3).ToString()))
{
dbPage.Element = ReadToEnd(result.GetStream(3));
}
return(dbPage);
}
public List <Models.User> GetName(Models.Search P_NAME)
{
List <Models.User> ret = new List <Models.User>();
BdUtil bdUtil = new BdUtil();
System.Data.Common.DbDataReader rs = bdUtil.Qry("select ID,NAME,AGE,ADDRESS from USERS where upper(name) like '%" + P_NAME.Name.ToUpperInvariant() + "%' order by NAME", null);
while (rs.Read())
{
ret.Add(new Models.User {
Id = rs.GetInt32(0), Name = rs.GetString(1), Age = rs.GetInt32(2), Address = rs.GetString(3)
});
}
return(ret);
}
public List <Models.User> GetAll()
{
List <Models.User> ret = new List <Models.User>();
BdUtil bdUtil = new BdUtil();
System.Data.Common.DbDataReader rs = bdUtil.Qry("select ID,NAME,AGE,ADDRESS from USERS order by NAME", null);
while (rs.Read())
{
ret.Add(new Models.User {
Id = rs.GetInt32(0), Name = rs.GetString(1), Age = rs.GetInt32(2), Address = rs.GetString(3)
});
}
return(ret);
}
public EmergentOrder QueryEmergentOrder(decimal id, OrderIdType orderIdType)
{
string sql = "SELECT Id,[Type],Content,[Time],Account,OrderIdType FROM T_EmergentOrder WHERE Id=@Id AND OrderIdType =@OrderIdType";
EmergentOrder result = null;
using (DbOperator dboperator = new DbOperator(Provider, ConnectionString))
{
dboperator.AddParameter("Id", id);
dboperator.AddParameter("OrderIdType", orderIdType);
using (System.Data.Common.DbDataReader reader = dboperator.ExecuteReader(sql))
{
if (reader.Read())
{
result = new EmergentOrder();
result.Id = reader.GetDecimal(0);
result.Type = (OrderStatus)reader.GetInt32(1);
result.Content = reader.GetString(2);
result.Time = reader.GetDateTime(3);
result.Account = reader.GetString(4);
result.OrderIdTypeValue = (OrderIdType)reader.GetByte(5);
}
}
}
return(result);
}
public dbUser GetUserSourceInfo(int user_id)
{
dbUser us = new dbUser();
string sql = $"SELECT users.id, users.name, sources.id, sources.upload_time FROM users,sources WHERE sources.user_id=users.id AND users.id={user_id} AND used=1 AND sources.status='ok'";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = sql;
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
us.id = reader.GetInt16(0);
us.name = reader.GetString(1);
us.compiledAndUsedSourceId = reader.GetInt16(2);
us.last_upload_time = reader.GetInt32(3);
}
}
}
return(us);
}
public Models.User GetId(int P_ID)
{
Models.User ret = new Models.User();
BdUtil bdUtil = new BdUtil();
System.Data.Common.DbDataReader rs = bdUtil.Qry("select ID,NAME,AGE,ADDRESS from USERS where ID = " + P_ID, null);
while (rs.Read())
{
ret.Id = rs.GetInt32(0);
ret.Name = rs.GetString(1);
ret.Age = rs.GetInt32(2);
ret.Address = rs.GetString(3);
}
return(ret);
}
public void frun()
{
try
{
using (SqlConnection connection = new SqlConnection(""))
{
connection.ConnectionString = @"Data Source=192.168.56.101,1433\SQLEXPRESS;Initial Catalog=mybase;Persist Security Info=True;User ID=User01;Password=User001";
connection.Open();
string text = "SELECT users.name, users.login, users.password, users.isadmin, users.id FROM users where users.id=" + this.id.ToString();
using (SqlCommand command = new SqlCommand(text, connection))
using (System.Data.Common.DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
textBox1.Text = reader.GetString(0);
textBox2.Text = reader.GetString(1);
textBox3.Text = reader.GetString(2);
int i = reader.GetInt32(3);
textBox4.Text = "Нет";
if (i == 1)
{
textBox4.Text = "Да";
}
}
}
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Ошибка работы с базой данных " + ex);
}
}
private static DBSerie ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBSerie dbSerie = new DBSerie();
dbSerie.ID = result.GetInt32(0);
dbSerie.Name = result.GetString(1);
if (!string.IsNullOrEmpty(result.GetValue(2).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(2).ToString()))
{
dbSerie.Image = ReadToEnd(result.GetStream(2));
}
dbSerie.Extension = result.GetString(3);
if (!string.IsNullOrEmpty(result.GetValue(4).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(4).ToString()))
{
dbSerie.ParentID = result.GetInt32(4);
}
return(dbSerie);
}
public static SMD getSMD(System.Data.Common.DbDataReader reader)
{
SMD SMD = new SMD();
SMD.StoreName = reader.GetString(reader.GetOrdinal("StoreName"));
SMD.MD_ = reader.GetString(reader.GetOrdinal("MD#"));
SMD.SMDURL = reader.GetString(reader.GetOrdinal("SMDURL"));
SMD.SMDPrice = reader.GetInt32(reader.GetOrdinal("SMDPrice"));
return(SMD);
}
public static int GetRefValue(System.Data.Common.DbDataReader reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(0);
}
else
{
return(reader.GetInt32(ordinal));
}
}
private void DoSearch()
{
try
{
_searching = true;
_isStop = false;
SetRecordText(null, txtRecord);
string sql = "select Count(*) from c2lp_optRecord " + _where;
int sum = Convert.ToInt32(BaseServer._SqlHelp.ExecuteScalar(sql, CommandType.Text));
if (sum == 0)
{
MessageBox.Show("没有查到符合条件的相关记录!");
return;
}
SetRecordText("共查到" + sum + "条记录.", txtRecord);
sql = sql.Replace("Count(*)", "optTime,optNumber,optType,optCustomerId") + " order by optTime desc ,optTypeId,optCustomerId";
using (System.Data.Common.DbDataReader r = BaseServer._SqlHelp.ExecuteReader(sql, CommandType.Text))
{
while (r.Read())
{
if (_isStop)
{
SetRecordText("已手动停止显示剩余记录.", txtRecord);
break;
}
DateTime optTime = r.GetDateTime(0);
string optNumber = r.GetString(1);
string optType = r.GetString(2);
int optCustomerId = r.GetInt32(3);
string optCustomer = "";
if (_dicConsignor.ContainsKey(optCustomerId))
{
optCustomer = _dicConsignor[optCustomerId];
}
SetRecordText(string.Format("{0} {1} {2} {3}", optTime.ToString("MM-dd HH:mm:ss"), optNumber, optType, optCustomer), txtRecord);
}
}
}
catch (Exception ex)
{
SetRecordText(ex.Message, txtRecord);
}
finally
{
_searching = false;
SetRecordText("查询完毕.", txtRecord);
SetRecordText("CNM", btnSearch);
}
}
public List <SignalAng> getAngleList()
{
// angle
List <SignalAng> angList = new List <SignalAng>();
String sql = "SELECT lampId, eAng, sAng, wAng, nAng FROM signalAng ";
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, myConn);
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SignalAng ang = new SignalAng();
ang.LampId = reader.GetString(0).ToUpper().Trim();//.Replace('-', '_');
ang.EAng = reader.GetInt32(1);
ang.SAng = reader.GetInt32(2);
ang.WAng = reader.GetInt32(3);
ang.NAng = reader.GetInt32(4);
angList.Add(ang);
}
reader.Close();
return(angList);
}
public List <SignalPos> getPosList()
{
// position// position
List <SignalPos> posList = new List <SignalPos>();
String sql = "SELECT lampId, compass, dir, x, y FROM signalPos";
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, myConn);
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SignalPos pos = new SignalPos();
pos.LampId = reader.GetString(0).Trim().ToUpper(); //.Replace('-', '_')
pos.Compass = reader.IsDBNull(1) ? 1 : reader.GetInt32(1);
pos.Dir = reader.IsDBNull(2) ? 1 : reader.GetInt32(2);
pos.X = reader.IsDBNull(3) ? 1 : reader.GetInt32(3);
pos.Y = reader.IsDBNull(4) ? 1 : reader.GetInt32(4);
posList.Add(pos);
}
reader.Close();
return(posList);
}
private static DBFichier ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBFichier dBFichier = new DBFichier();
dBFichier.ID = result.GetInt32(0);
dBFichier.Name = result.GetString(1);
if (!string.IsNullOrEmpty(result.GetValue(2).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(2).ToString()))
{
dBFichier.Order = result.GetInt32(2);
}
if (!string.IsNullOrEmpty(result.GetValue(3).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(3).ToString()))
{
dBFichier.Image = ReadToEnd(result.GetStream(3));
}
if (!string.IsNullOrEmpty(result.GetValue(4).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(4).ToString()))
{
dBFichier.ParentID = result.GetInt32(4);
}
if (!string.IsNullOrEmpty(result.GetValue(5).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(5).ToString()))
{
dBFichier.Collection = result.GetString(5);
}
return(dBFichier);
}
public List <Game> GetWaitGame(string type)
{
List <Game> games_list = new List <Game>();
string sql = $"SELECT * FROM {type} WHERE status='wait'";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = sql;
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Game sb = new Game();
sb.id = reader.GetInt16(0);
sb.datetime = reader.GetInt32(1);
UserGroup ug = new UserGroup();
ug.group_id = reader.GetInt16(2);
sb.usergroup = ug;
sb.status = reader.GetString(3);
if (type == "sandbox")
{
dbUser us = new dbUser();
us.id = reader.GetInt16(4);
sb.creator = us;
}
games_list.Add(sb);
}
}
}
for (int i = 0; i < games_list.Count; i++)
{
games_list[i].usergroup = GetUsersInGroup(games_list[i].usergroup.group_id);
games_list[i].creator = GetUserSourceInfo(games_list[i].creator.id);
}
return(games_list);
}
static string AsSqlUpdate(System.Data.Common.DbDataReader result, string tableName)
{
var sb = new System.Text.StringBuilder();
sb.AppendFormat("UPDATE {0} SET", tableName);
int id=0;
for (int i=0; i < result.FieldCount; i++)
{
string name = result.GetName(i);
if (name=="Id")
{
id = result.GetInt32(i);
}
else
{
sb.AppendFormat("\r\n\t{0} = {1},", name, ValueToSqlString(result.GetValue(i)));
}
}
sb.Length -= 1; // remove last comma
sb.AppendFormat("\r\nWHERE Id = {0}\r\nGO\r\n", id);
return sb.ToString();
}
private static Domain.IncomeGroupLimitGroup loadIncomeGroup(System.Data.Common.DbDataReader reader)
{
Domain.IncomeGroupLimitGroup income = new Domain.IncomeGroupLimitGroup();
income.Limitation = new List <Domain.IncomeGroupLimit>();
Domain.IncomeGroupLimit limit = null;
while (reader.Read())
{
var currentId = reader.GetGuid(0);
if (income.Id != currentId)
{
income.Id = currentId;
income.CompanyId = reader.GetGuid(1);
income.IncomeGroupId = reader.IsDBNull(2) ? (Guid?)null : reader.GetGuid(2);
income.Remark = reader.GetString(3);
income.Limitation = new List <Domain.IncomeGroupLimit>();
}
var currlimitId = reader.GetGuid(4);
if (!income.Limitation.Any(it => it.Id == currlimitId))
{
limit = new Domain.IncomeGroupLimit();
limit.Id = currlimitId;
limit.IncomeId = reader.GetGuid(5);
limit.Type = (Common.Enums.PeriodType)reader.GetByte(6);
limit.Price = reader.GetInt32(7);
limit.Airlines = reader.GetString(12);
limit.IsOwnerPolicy = reader.GetBoolean(13);
limit.Period = new List <Domain.IncomeGroupPeriod>();
income.Limitation.Add(limit);
}
Domain.IncomeGroupPeriod period = new Domain.IncomeGroupPeriod();
period.DeductId = reader.GetGuid(8);
period.StartPeriod = reader.GetDecimal(9);
period.EndPeriod = reader.GetDecimal(10);
period.Period = reader.GetDecimal(11);
limit.Period.Add(period);
}
return(income);
}
public List <SignalProp> getSignalList()
{
List <SignalProp> list = new List <SignalProp>();
String sql = " SELECT lampId, signalName, type, ip FROM signal WHERE type IN (1, 4, 5) ";
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, myConn);
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SignalProp prop = new SignalProp();
prop.SignalID = reader.GetString(0).Trim().ToUpper(); //.Replace('-', '_')
prop.SignalName = DBStringToNormal(reader.GetString(1).Trim());
prop.Type = reader.IsDBNull(2) ? (byte)1 : (byte)reader.GetInt32(2);
prop.Ip = reader.IsDBNull(3) ? "" : reader.GetString(3).Trim();
list.Add(prop);
}
reader.Close();
return(list);
}
private int GetInt(System.Data.Common.DbDataReader RS, String FiledName)
{
int FieldID = RS.GetOrdinal(FiledName);
return(RS.IsDBNull(FieldID) ? 0 : RS.GetInt32(FieldID));
}
public EmergentOrder QueryEmergentOrder(decimal id, DataTransferObject.Order.OrderStatus status)
{
EmergentOrder emergentOrder = new EmergentOrder();
string sql = "select Id,[Type],Content,[Time],Account from T_EmergentOrder WHERE Id =@Id AND [Type] = @Type";
using (DbOperator dboperator = new DbOperator(Provider, ConnectionString))
{
dboperator.AddParameter("Id", id);
dboperator.AddParameter("Type", status);
using (System.Data.Common.DbDataReader reader = dboperator.ExecuteReader(sql))
{
if (reader.Read())
{
emergentOrder.Id = reader.GetDecimal(0);
emergentOrder.Type = (DataTransferObject.Order.OrderStatus)reader.GetInt32(1);
emergentOrder.Content = reader.GetString(2);
emergentOrder.Time = reader.GetDateTime(3);
emergentOrder.Account = reader.GetString(4);
}
}
}
return(emergentOrder);
}
/// <summary>
/// Gets an int column.
/// </summary>
/// <param name="reader"></param>
/// <param name="ordinal"></param>
/// <returns></returns>
public static Int32?GetNInt32(this System.Data.Common.DbDataReader reader, int ordinal)
{
return(reader.IsDBNull(ordinal) ? (int?)null : reader.GetInt32(ordinal));
}
/// <summary>
/// Gets an int column. Slower then GetOrdinal + GetInt32
/// </summary>
/// <param name="reader"></param>
/// <param name="name"></param>
/// <returns></returns>
public static Int32?GetNInt32(this System.Data.Common.DbDataReader reader, string name)
{
int ordinal = reader.GetOrdinal(name);
return(reader.IsDBNull(ordinal) ? (int?)null : reader.GetInt32(ordinal));
}
/// <summary>
/// Gets an int column. Slower then GetOrdinal + GetInt32
/// </summary>
/// <param name="reader"></param>
/// <param name="name"></param>
/// <returns></returns>
public static Int32 GetInt32(this System.Data.Common.DbDataReader reader, string name)
{
int ordinal = reader.GetOrdinal(name);
return(reader.GetInt32(ordinal));
}
