public IEnumerable <Property> GetColumns(string workSheet)
{
using (OleDbConnection connection = _connectionBuilder.GetConnection(_fileConfiguration.FileName))
{
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = String.Format("SELECT * FROM [{0}]", workSheet);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
// Empty .xls worksheet returns F1
if (reader.FieldCount == 1 && reader.GetName(0) == "F1")
{
yield break;
}
for (int i = 0; i < reader.FieldCount; i++)
{
yield return(new Property(reader.GetFieldType(i).ToString(),
reader.GetName(i)));
}
}
}
}
}
}
public static Article SelectByRef(int Ref)
{
string referenceSelectQuery = "SELECT * FROM Article WHERE Reference=?";
OleDbCommand referenceSelectCommand = new OleDbCommand(referenceSelectQuery, db); //_ Command Object.
referenceSelectCommand.Parameters.Add(new OleDbParameter("Reference", Ref));
OleDbDataReader ArticleRead = null; //_ Structures Receiving Object.
try //_ Query Execution.
{
ArticleRead = referenceSelectCommand.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
Article searchedArticle = null; //_ Instanciated Object for Structures Receiving (We admit that there is only one structure thanks to primary key constraint).
if (ArticleRead != null)
{
if (ArticleRead.Read())
{
searchedArticle = new Article(); //_ Instanciation.
try //_ Reference Filling.
{
searchedArticle.Reference = Int32.Parse(ArticleRead[0].ToString());
}
catch (Exception ex)
{
Console.WriteLine("SelectByRef : " + ArticleRead.GetName(0) + " : ");
throw ex;
}
try //_ Designation Filling.
{
searchedArticle.Designation = ArticleRead[1].ToString();
}
catch (Exception ex)
{
Console.WriteLine("SelectByRef : " + ArticleRead.GetName(1) + " : ");
throw ex;
}
try //_ Price Filling.
{
searchedArticle.Price = float.Parse(ArticleRead[2].ToString());
}
catch (Exception ex)
{
Console.WriteLine("SelectByRef : " + ArticleRead.GetName(2) + " : ");
throw ex;
}
}
}
return(searchedArticle);
}
public string GetTypeColumn(string connectionOleString, string tableName)
{
using (OleDbConnection connectionOle = new OleDbConnection(connectionOleString))
{
OleDbCommand commandOle = new OleDbCommand("Select * from " + tableName, connectionOle);
connectionOle.Open();
OleDbDataReader readerOle = commandOle.ExecuteReader();
string commandForCreatTable = "(id int identity(1,1) primary key,";
for (int c = 1; c < readerOle.VisibleFieldCount; c++)
{
System.Type type = readerOle.GetFieldType(c);
string temp = type.Name;
if (temp.Contains("Int"))
{
commandForCreatTable = commandForCreatTable + readerOle.GetName(c) + " int,";
}
/*
* if (temp.Contains("Boolean"))
* {
*
* }*/
if (temp.Contains("String"))
{
commandForCreatTable = commandForCreatTable + readerOle.GetName(c) + " nvarchar(50),";
}
}
commandForCreatTable = commandForCreatTable.Remove(commandForCreatTable.Length - 1, 1);
commandForCreatTable = commandForCreatTable + ")";
return(commandForCreatTable);
}
}
/// <summary>
/// 查询数据记录
/// </summary>
/// <param name="sql">查询SQL</param>
/// <returns>返回第一行查询数据</returns>
public Dictionary <string, object> Find(string sql)
{
try
{
Dictionary <string, object> res = new Dictionary <string, object>();
OleDbConnection conn = (OleDbConnection)GetConnection();
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.CommandTimeout = this.Timeout * 1000;
OleDbDataReader da = cmd.ExecuteReader();
if (da.Read())
{
if (da.FieldCount > 0)
{
for (int i = 0; i < da.FieldCount; i++)
{
if (da.IsDBNull(i))
{
res.Add(da.GetName(i), null);
}
else
{
res.Add(da.GetName(i), da[i]);
}
}
}
}
da.Close();
CloseConnection(conn);
return(res);
}
catch (Exception ex)
{
throw ex;
}
}
// load the data
public Dictionary <string, string> load(int id)
{
string sql = "select * from admin where id=@id";
OleDbConnection con = getCon(null);
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbParameter param = new OleDbParameter("@id", id);
cmd.Parameters.Add(param);
OleDbDataReader reader = cmd.ExecuteReader();
Dictionary <string, string> map = null;
if (reader.Read())
{
map = new Dictionary <string, string>();
map.Add(reader.GetName(0), reader.GetInt32(0) + "");
map.Add(reader.GetName(1), reader.GetString(1));
map.Add(reader.GetName(2), reader.GetString(2));
map.Add(reader.GetName(3), reader.GetInt32(3) + "");
}
//Console.WriteLine("{0} {1} {2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));
// Console.Read();
//close the con
closeAll(con, cmd, reader);
return(map);
}
// load by name
public Dictionary <string, string> loadByName(string uname)
{
string sql = "select * from admin where uname=@uname";
OleDbConnection con = getCon(null);
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbParameter param = new OleDbParameter("@uname", uname);
cmd.Parameters.Add(param);
Dictionary <string, string> map = new Dictionary <string, string>();
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
map = new Dictionary <string, string>();
map.Add(reader.GetName(0), reader.GetInt32(0) + "");
map.Add(reader.GetName(1), reader.GetString(1));
map.Add(reader.GetName(2), reader.GetString(2));
map.Add(reader.GetName(3), reader.GetInt32(3) + "");
}
// close the con
closeAll(con, cmd, reader);
return(map);
}
//UpiBinSelect
public JArray SelectUpiBinTable(string strTableName, string strCardNo)
{
JArray arrRet = new JArray();
JObject jsonRet = null;
try
{
string strQuery = " SELECT * FROM BIN WHERE '" + strCardNo + "' LIKE BIN + '%' ";
strQuery += " AND PAN_LENGTH ='" + strCardNo.Length + "' ";
OleDbCommand cmd = new OleDbCommand(strQuery, m_ObjCon);
OleDbDataReader oleData = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (oleData.Read())
{
jsonRet = new JObject();
for (int i = 0; i < oleData.FieldCount; i++)
{
jsonRet.Add(oleData.GetName(i), oleData[oleData.GetName(i)].ToString());
}
arrRet.Add(jsonRet);
}
}
catch (Exception e)
{
m_logger.Error(e.StackTrace);
}
return(arrRet);
}
private void GetData(string tablename, string sSql, string sDisplayField)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
try
{
using (OleDbConnection sqlconn = new OleDbConnection(CommonClass.sDBConnectionString))
{
OleDbCommand sqlcom = new OleDbCommand("select " + sDisplayField + " from " + tablename + " where 线编号='" +
CommonClass.listDIC[0].sLineCode +
"' and 行别='" + CommonClass.listDIC[0].sDir + "' " + sSql, sqlconn);
sqlconn.Open();
OleDbDataReader oddr = sqlcom.ExecuteReader();
for (int i = 0; i < oddr.VisibleFieldCount; i++)
{
dataGridView1.Columns.Add(oddr.GetName(i), oddr.GetName(i));
}
while (oddr.Read())
{
object[] o = new object[oddr.VisibleFieldCount];
oddr.GetValues(o);
dataGridView1.Rows.Add(o);
}
sqlconn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//CommonClass.listDIC[0].sLineCode
}
public JArray get_data_AccessDB(string strTableName)
{
JArray arrRet = new JArray();
try
{
String Query = string.Empty;
Query = String.Format("SELECT * FROM " + strTableName);
OleDbCommand OLECmd = new OleDbCommand(Query, m_ObjCon);
OLECmd.CommandType = CommandType.Text;
OleDbDataReader OLEReader = OLECmd.ExecuteReader(CommandBehavior.Default);
JObject jsonRet = null;
while (OLEReader.Read())
{
jsonRet = new JObject();
for (int i = 0; i < OLEReader.FieldCount; i++)
{
jsonRet.Add(OLEReader.GetName(i), OLEReader[OLEReader.GetName(i)].ToString());
}
arrRet.Add(jsonRet);
}
OLECmd.Dispose();
OLEReader.Dispose();
}
catch (Exception ex)
{
m_logger.Error(ex.StackTrace);
}
return(arrRet);
}
//table 조회
//strTableName : table 명
//jsonWhere : where 조건
//jsonOrder : order by 조건
public JArray SelectTableOrderCounry(string strTableName, JObject jsonWhere = null, JObject jsonOrder = null)
{
JArray arrRet = new JArray();
JObject jsonRet = null;
try
{
IList <string> keys = null;
IList <string> keyOrders = null;
string strQuery = " SELECT * FROM CODE WHERE CODEDIV ='COUNTRY_CODE' ORDER BY COMCODE ";
OleDbCommand cmd = new OleDbCommand(strQuery, m_ObjCon);
OleDbDataReader oleData = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (oleData.Read())
{
jsonRet = new JObject();
for (int i = 0; i < oleData.FieldCount; i++)
{
jsonRet.Add(oleData.GetName(i), oleData[oleData.GetName(i)].ToString());
}
arrRet.Add(jsonRet);
}
}
catch (Exception e)
{
m_logger.Error(e.StackTrace);
}
return(arrRet);
}
private void button3_Click(object sender, EventArgs e)
{
dataGridView1.Columns.Clear();
dataGridView1.Rows.Clear();
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
String qry = "select Train.TrainID, TrainType, StationName, DepartureTime, ArrivalTime" +
" from ((ScheduleStation inner join TrainSchedule on ScheduleStation.TrainScheduleID=TrainSchedule.TrainScheduleID)" +
" inner join Train on TrainSchedule.TrainID=Train.TrainID)" +
" inner join Station on ScheduleStation.StationID=Station.StationID" +
" where StationName =?";
command.CommandText = qry;
command.Parameters.AddWithValue("@p1", comboBox1.SelectedItem.ToString());
OleDbDataReader reader = command.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
dataGridView1.Columns.Add(reader.GetName(i), reader.GetName(i));
}
while (reader.Read())
{
dataGridView1.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString());
}
connection.Close();
}
/// <summary>
/// データベースのデータをフェッチします。
/// </summary>
/// <param name="connection">データベースのコネクション</param>
public void Fetch(OleDbConnection connection)
{
_connection = connection;
Stopwatch sw = Stopwatch.StartNew();
OleDbCommand command = new OleDbCommand(string.Format(FetchQuery, Name), connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, ISerializedData> serializedData = new Dictionary <string, ISerializedData>();
for (int i = 0; i < reader.FieldCount; i++)
{
serializedData.Add(reader.GetName(i),
new SerializedData(reader.GetName(i), Type.GetTypeCode(reader.GetFieldType(i)),
reader.GetValue(i)));
}
T dataModel = ModelFactory <T> .Factory();
dataModel.Deserialize(serializedData);
_data.Add(dataModel);
}
sw.Stop();
ObjectDatabase._logger.QueryLog($"Fetch Exec Query {command.CommandText}");
ObjectDatabase._logger.OperationLog($"Fetch {sw.ElapsedMilliseconds}ms");
reader.Close();
command.Dispose();
}
public void fetchStyleSizes()
{
string query = "SELECT B.SZ013G AS SIZE0,B.SZ023G AS SIZE2,B.SZ033G AS SIZE4,B.SZ043G AS SIZE6,B.SZ053G AS SIZE8,B.SZ063G AS SIZE10," +
"B.SZ073G AS SIZE12,B.SZ083G AS SIZE14,B.SZ093G AS SIZE16,B.SZ103G AS SIZE18,B.SZ113G AS SIZE20,B.SZ123G AS BULK " +
"FROM PRHDMS0 AS A LEFT JOIN SZSCMS0 AS B ON A.SZCD3K=B.SZCD3G AND A.CONO3K=B.CONO3G " +
"WHERE A.CONO3K=1 AND A.PRCD3K='" + this.styleCode.ToUpper() + "'";
try
{
using (OleDbConnection conn = new OleDbConnection(as400conn))
{
OleDbCommand command = new OleDbCommand(query, conn);
conn.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
for (int index = 0; index < reader.FieldCount; index++)
{
string columnName = reader.GetName(index);
this.productSizes[reader.GetName(index)] = reader.GetValue(index).ToString();
}
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
private static void showResultats()
{
DbConnection dbCon = DbConnection.GetInstance();
OleDbDataReader reader = dbCon.GetOleDbDataReader("SELECT * FROM resultats");
string g = String.Format("|{0,-20}|{1,-20}|{2,-20}|",
reader.GetName(0),
reader.GetName(1),
reader.GetName(2));
Console.WriteLine(g);
while (reader.Read())
{
//Console.WriteLine(reader[0].ToString());
g = String.Format("|{0,-20}|{1,-20}|{2,-20}|",
reader[0].ToString(),
reader[1].ToString(),
reader[2].ToString());
Console.WriteLine(g);
}
reader.Close();
}
private static string InsertRowString(string tableName, Dictionary <string, string> valueDict, OleDbDataReader read)
{
var sb = new StringBuilder();
//bool firstcol = true;
sb.AppendFormat("INSERT INTO [{0}](", tableName);
for (int i = 0; i < read.FieldCount; ++i)
{
if (i != 0)
{
sb.Append(",");
}
sb.Append(read.GetName(i));
}
sb.Append(") VALUES(");
for (int i = 0; i < read.FieldCount; ++i)
{
sb.Append("@" + read.GetName(i));
if (i != read.FieldCount - 1)
{
sb.Append(",");
}
}
sb.Append(")");
return(sb.ToString());
}
//Si le bouton "Se connecter" est cliqué.
protected void ButtonConnect_Click(object sender, EventArgs e)
{
string lastName = TextBoxLastName.Text;
string firstName = TextBoxFirstName.Text;
if (Session["modeleClient"] != null)
{
//On le récupère et on demande les enregistrements des clients selon la requête passée en paramètre
Modele modele = (Modele)Session["modeleClient"];
OleDbDataReader readerSelect = modele.ReadClient("SELECT * FROM CLIENT WHERE iDTypeClient = 4");
string iD = readerSelect.GetName(1);
string iD2 = readerSelect.GetName(2);
while (readerSelect.Read())
{
if (lastName == readerSelect[iD].ToString() && firstName == readerSelect[iD2].ToString())
{
//<JCOTE>
PanelAdmin.Visible = false;
ImageButtonPasserUneCommande.Enabled = true;
ImageButtonPasserUneCommande.Visible = true;
ImageButtonDeconnection.Enabled = true;
ImageButtonDeconnection.Visible = true;
Session["adminConnected"] = true;
//</JCOTE>
}
}
readerSelect.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Visual Projects\Db_Labs\Lab_7\Lab_6.mdb";//Db_Labs
cmd.Connection = cn;
textBox1.BackColor = Color.FromArgb(255, 255, 255);
try
{
cn.Open(); // установка соединения
}
catch
{
textBox1.Text = "Ошибка подключения!";
textBox1.BackColor = Color.FromArgb(255, 100, 100);
}
finally
{
try
{
cmd.CommandText = "SELECT * FROM [" + textBox1.Text + "];";
OleDbDataReader rd = cmd.ExecuteReader();// .schema
List <string[]> data = new List <string[]>();
// если запрос вернул результат
if (rd.HasRows)
{
clearDataGridView(dataGridView1);
nameTable = textBox1.Text;
for (int i = 0; i < rd.FieldCount; ++i)
{
dataGridView1.Columns.Add(rd.GetName(i), rd.GetName(i));
}
while (rd.Read())
{
// ... добавляем в список содержимое столбца «Фамилия»
data.Add(new string[rd.FieldCount]);
for (int i = 0; i < rd.FieldCount; ++i)
{
data[data.Count - 1][i] = rd[i].ToString();
}
}
foreach (string[] s in data)
{
dataGridView1.Rows.Add(s);
}
}
}
catch
{
textBox1.Text = "Ошибка подключения!";
textBox1.BackColor = Color.FromArgb(255, 100, 100);
}
finally
{
cn.Close(); // закрытие соединения с БД
}
}
}
public static DataTable ReadAllData(string tableName, string mdbPath, ref bool success)
{
DataTable dt = new DataTable();
try
{
DataRow dr;
//1、建立连接 C#操作Access之读取mdb
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ mdbPath + ";";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接 C#操作Access之读取mdb
odcConnection.Open();
//建立SQL查询
OleDbCommand odCommand = odcConnection.CreateCommand();
//3、输入查询语句 C#操作Access之读取mdb
odCommand.CommandText = "select * from " + tableName + " order by 开始时间 desc";
//建立读取
OleDbDataReader odrReader = odCommand.ExecuteReader();
//查询并显示数据
int size = odrReader.FieldCount;
for (int i = 0; i < size; i++)
{
DataColumn dc;
dc = new DataColumn(odrReader.GetName(i));
dt.Columns.Add(dc);
}
while (odrReader.Read())
{
dr = dt.NewRow();
for (int i = 0; i < size; i++)
{
dr[odrReader.GetName(i)] =
odrReader[odrReader.GetName(i)].ToString();
}
dt.Rows.Add(dr);
}
//关闭连接 C#操作Access之读取mdb
odrReader.Close();
odcConnection.Close();
success = true;
return(dt);
}
catch
{
success = false;
return(dt);
}
}
public string GetAsString(string Row)
{
try
{
ExError = new Exception();
switch (ConType)
{
case 1:
{
if (!Odbcdr.IsClosed)
{
for (int i = 0; i < Odbcdr.FieldCount; i++)
{
if (Odbcdr.GetName(i).ToUpper() == Row.ToUpper())
{
return(Odbcdr[i].ToString());
}
}
}
} break;
case 2:
{
if (!OleDbdr.IsClosed)
{
for (int i = 0; i < OleDbdr.FieldCount; i++)
{
if (OleDbdr.GetName(i).ToUpper() == Row.ToUpper())
{
return(OleDbdr[i].ToString());
}
}
}
} break;
case 3:
{
if (!Sqldr.IsClosed)
{
for (int i = 0; i < Sqldr.FieldCount; i++)
{
if (Sqldr.GetName(i).ToUpper() == Row.ToUpper())
{
return(Sqldr[i].ToString());
}
}
}
} break;
}
return(null);
}
catch (Exception x)
{
ExError = x;
LogWindow.SqlLog.LogAdd(ExError.Message);
return(null);
}
}
static void InsertMethod()
{
Console.Write("Введите intModelID: ");
int intModelID = Int32.Parse(Console.ReadLine());
Console.Write("Введите strName: ");
string strName = Console.ReadLine();
Console.Write("Введите intManufacturerID: ");
int intManufacturerID = Int32.Parse(Console.ReadLine());
Console.Write("Введите intSMCSFamilyID: ");
int intSMCSFamilyID = Int32.Parse(Console.ReadLine());
Console.Write("Введите strImage: ");
string strImage = Console.ReadLine();
string query = String.Format("INSERT INTO [TablesModel] " +
"(intModelID, strName, intManufacturerID, intSMCSFamilyID, strImage) " +
"VALUES ({0}, '{1}', {2}, {3}, '{4}')",
intModelID, strName, intManufacturerID, intSMCSFamilyID, strImage);
using (OleDbConnection connection = new OleDbConnection(_connectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand(query, connection);
int number = cmd.ExecuteNonQuery();
Console.WriteLine("\nДобавлено объектов: {0}", number);
Console.WriteLine();
Console.WriteLine("----------------------------");
Console.WriteLine("Добавленные объекты:\n");
query = "SELECT * FROM [TablesModel]";
cmd.CommandText = query;
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
object objIntModelID = reader.GetValue(0);
object objStrName = reader.GetValue(1);
object objIntManufacturerID = reader.GetValue(2);
object objIntSMCSFamilyID = reader.GetValue(3);
object objStrImage = reader.GetValue(4);
Console.WriteLine(reader.GetName(0) + " : " + objIntModelID);
Console.WriteLine(reader.GetName(1) + " : " + objStrName);
Console.WriteLine(reader.GetName(2) + " : " + objIntManufacturerID);
Console.WriteLine(reader.GetName(3) + " : " + objStrName);
Console.WriteLine(reader.GetName(4) + " : " + objStrImage);
Console.WriteLine();
}
}
}
}
private void showX(string text)
{
if ("".Equals(DBHelper.route))
{
return;
}
if ("".Equals(text))
{
return;
}
OleDbConnection myconn = DBHelper.Connection;
string sql = "SELECT * FROM " + text /*+"班级基本情况"*/;
OleDbDataReader mySql = DBHelper.GetReader(sql);
if (dataGridView1.Rows.Count > 0)
{
dataGridView1.Rows.Clear();
}
if (dataGridView1.Columns.Count > 0)
{
dataGridView1.Columns.Clear();
}
for (int i = 0; i < mySql.FieldCount; i++)
{
DataGridViewTextBoxColumn acCode = new DataGridViewTextBoxColumn();
acCode.Name = mySql.GetName(i);
acCode.DataPropertyName = mySql.GetName(i);
acCode.HeaderText = mySql.GetName(i);
dataGridView1.Columns.Add(acCode);
}
int record = 0;
while (mySql.Read())
{
record++;
int index = dataGridView1.Rows.Add();
int p = 0;
while (mySql.FieldCount > p)
{
dataGridView1.Rows[index].Cells[p].Value = mySql[p++];
}
}
if (record == 0)
{
int index = dataGridView1.Rows.Add();
int p = 0;
dataGridView1.Rows[index].Cells[p++].Value = "暂无数据";
dataGridView1.Rows[index].Cells[p++].Value = "暂无数据";
dataGridView1.Rows[index].Cells[p++].Value = "暂无数据";
}
DBHelper.connection.Close();
DBHelper.connection = null;
}
/// <summary>
/// DataInitialize Event
/// Use it to add custom fields to the report's Fields collection.
/// Custom fields can be added to a bound report (one that uses a Data Control to connect and retrieve records)
/// or an unbound report (one that does not depend on a data control to get its records).
/// </summary>
private void Invoice_DataInitialize(object sender, EventArgs eArgs)
{
//Add all the columns from the database as fields for the report.
for (int i = 0; i < _InvoiceDataReader.FieldCount; i++)
{
Fields.Add(_InvoiceDataReader.GetName(i));
}
//Add calculated fields
Fields.Add("DiscountTotal");
}
private void Form2_Load(object sender, EventArgs e)
{
con = new OleDbConnection("Provider=SqloleDb;Data Source=SERVER;User Id=Sa;Password=123;DataBase=CS4DB");
con.Open();
cmd = new OleDbCommand("select * from dept order by deptno", con);
dr = cmd.ExecuteReader();
lblDno.Text = dr.GetName(0) + ":";
lblDname.Text = dr.GetName(1) + ":";
lblLoc.Text = dr.GetName(2) + ":";
Show_Data();
}
public List <string> get_Headers(string comm)
{
OleDbCommand command = new OleDbCommand(comm, this.connection);
OleDbDataReader reader = command.ExecuteReader();
List <string> resutl = new List <string>();
resutl.Add(reader.GetName(0));
resutl.Add(reader.GetName(1));
resutl.Add(reader.GetName(2));
resutl.Add(reader.GetName(3));
return(resutl);
}
protected virtual T GetInstance(ref OleDbDataReader rdr)
{
T _t = Activator.CreateInstance <T>();
try
{
PropertyInfo[] propertyInfos = _t.GetType().GetProperties();
foreach (var property in propertyInfos)
{
string name = string.Empty;
for (int i = 0; i < rdr.FieldCount; i++)
{
if (property.Name.ToUpper() == rdr.GetName(i).ToUpper())
{
name = rdr.GetName(i);
break;
}
}
if (!string.IsNullOrEmpty(name))
{
object _value = rdr[name];
if (_value != null && _value != DBNull.Value)
{
if (property.PropertyType.Name == "Single")
{
property.SetValue(_t, Convert.ToSingle(_value), null);
}
else if (property.PropertyType.Name == "Date")
{
property.SetValue(_t, Convert.ToDateTime(_value), null);
}
else
{
property.SetValue(_t, _value, null);
}
}
}
}
}
catch (Exception ex)
{
Logger.LogError4Exception(ex, "AppLogger");
}
//RichModelLazyLoader(ref _t);
return(_t);
}
public BackData doSQLFind(PBase _pBase = null)
{
BackData backData = new BackData();
string tabName = _pBase.tabName;
string strCom = "Select * from " + tabName;
//try
//{
OleDbConnection conn = getConn();
conn.Open();
if (_pBase != null)
{
Dictionary <string, string> param = classToDic(_pBase);
strCom += " where ";
foreach (string field in param.Keys)
{
if (field != "tabName" && field != "act" && field != "result")
{
strCom += field + "='" + param[field] + "' and ";
}
}
}
strCom = strCom.Substring(0, strCom.Length - 5);
OleDbCommand myCommand = new OleDbCommand(strCom, conn);
OleDbDataReader reader = myCommand.ExecuteReader();
PBase _tempTool;
while (reader.Read())
{
_tempTool = PFactory.instance.stringToClass(new string[] { "tabName", tabName });
for (int i = 0; i < reader.FieldCount; i++)
{
if (_tempTool.GetType().GetProperty(reader.GetName(i)) != null)
{
_tempTool.GetType().GetProperty(reader.GetName(i)).SetValue(_tempTool, reader.GetValue(i).ToString());
}
}
_tempTool.tabName = tabName;
backData.data.Add(_tempTool);
}
conn.Close();
//}
//catch (Exception e)
//{
// throw (new Exception("数据库更新出错:" + strCom + "\r" + e.Message));
//}
return(backData);
}
public override void getOvercuts(String productCode, String colorCode, String orderFrom = "", String orderTo = "")
{
String query = buildQueryOrder(productCode, colorCode, orderFrom, orderTo);
try
{
using (OleDbConnection conn = new OleDbConnection(as400conn))
{
OleDbCommand command = new OleDbCommand(query, conn);
conn.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
noRows = false;
while (reader.Read())
{
for (int index = 0; index < reader.FieldCount; index++)
{
string columnName = reader.GetName(index);
overcutvalues[reader.GetName(index)] = reader.GetValue(index).ToString();
if (columnName == "UNITSTOTAL")
{
this.totalUnits = Int32.Parse(reader.GetValue(index).ToString());
}
if (columnName != "UNITSTOTAL" && columnName != "PRODUCTCODE" && columnName != "COLORCODE")
{
this.unitsBySize[columnName] = Int32.Parse(reader.GetValue(index).ToString());
}
}
}
}
else
{
MessageBox.Show("no sales found for this style for E-Commerce");
}
reader.Close();
command.Dispose();
conn.Close();
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
/// <summary>
/// 读取mdb数据单张表所有数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="mdbPath">MDB地址</param>
/// <param name="success"></param>
/// <returns></returns>
public static DataTable ReadAllData(string tableName, ref bool success)
{
DataTable dt = new DataTable();
try
{
DataRow dr;
//1、建立连接
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=bbq;";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommand odCommand = odcConnection.CreateCommand();
//3、输入查询语句
string sql = "select " + tableName + ".[id]," + tableName + ".[issue]," + tableName + ".[num_group] from " + tableName + ",(select TOP 15 " + tableName + ".[id] from " + tableName + " order by " + tableName + ".[id] desc)a where " + tableName + ".[id]=a.[id] order by " + tableName + ".[id] asc";
odCommand.CommandText = sql;
//建立读取
OleDbDataReader odrReader = odCommand.ExecuteReader();
//查询并显示数据
int size = odrReader.FieldCount;
for (int i = 0; i < size; i++)
{
DataColumn dc;
dc = new DataColumn(odrReader.GetName(i));
dt.Columns.Add(dc);
}
while (odrReader.Read())
{
dr = dt.NewRow();
for (int i = 0; i < size; i++)
{
dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
}
dt.Rows.Add(dr);
}
//关闭连接
odrReader.Close();
odcConnection.Close();
success = true;
return(dt);
}
catch
{
success = false;
return(dt);
}
}
public DataTable ExecToDataTable(string ASql)
{
//使用上面定义的方法传入查询数语句 dr
OleDbDataReader dr = Exec(ASql);
//从内存中创建一个空表 dt
DataTable dt = new DataTable();
//如果dr语句查出来则遍历到dt表
if (dr.HasRows)
{
for (int i = 0; i < dr.FieldCount; i++)
{
//定义一个dc表示dt列的架构，并把dt列添加到dc
DataColumn dc = dt.Columns.Add(dr.GetName(i));
}
//关闭dt
dt.Rows.Clear();
}
//循环查询到的数据
while (dr.Read())
{
//在内存中创建一个row存储dt的每一行
DataRow row = dt.NewRow();
//遍历dr的每一行
for (int i = 0; i < dr.FieldCount; i++)
{
row[i] = dr[i];
}
//表dt中的每一行添加到刚刚定义好的row中
dt.Rows.Add(row);
}
//返回dt
return(dt);
}
public static DataTable ConvertDataReaderToDataTable(OleDbDataReader reader)
{
var objDataTable = new DataTable("TmpDataTable");
try
{
var intFieldCount = reader.FieldCount; //获取当前行中的列数；
for (var intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
//populate datatable
objDataTable.BeginLoadData();
//object[] objValues = new object[intFieldCount -1];
var objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return(objDataTable);
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
