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); } }