/// <summary> /// Queries the database and populates a <see cref="DataTable" /> with the resulting data from the specified SQL /// statement. /// </summary> /// <param name="source">The process application reference.</param> /// <param name="commandText">The command text.</param> /// <returns> /// Returns a <see cref="DataTable" /> representing the records returned by the command text. /// </returns> /// <exception cref="ArgumentNullException">commandText</exception> public static DataTable ExecuteQuery(this IMMPxApplication source, string commandText) { if (source == null) { return(null); } if (commandText == null) { throw new ArgumentNullException("commandText"); } var table = new DataTable(); table.Locale = CultureInfo.InvariantCulture; using (var cr = new ComReleaser()) { Recordset recordset = new RecordsetClass(); recordset.Open(commandText, source.Connection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 0); cr.ManageLifetime(recordset); var adapter = new OleDbDataAdapter(); adapter.Fill(table, recordset); recordset.Close(); } return(table); }
//DataTable转RecordSet public Recordset DsToRs(DataTable table) { Recordset rs = new RecordsetClass(); System.Array ArrA = System.Array.CreateInstance(typeof(string), table.Columns.Count); foreach (DataColumn dc in table.Columns) { ArrA.SetValue(dc.ColumnName, dc.Ordinal); rs.Fields._Append(dc.ColumnName, GetDataType(dc.DataType), -1, FieldAttributeEnum.adFldIsNullable); } rs.Open(Missing.Value, Missing.Value, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, -1); //rs.Open(null,null, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 0); // int i = 0; foreach (DataRow dr in table.Rows) { rs.AddNew(Missing.Value, Missing.Value); //object o; // rs.AddNew(rs.Fields.,dr.ItemArray); //rs.AddNew(ArrA, dr.ItemArray); for (int i = 0; i < table.Columns.Count; i++) { rs.Fields[i].Value = dr[i]; } } return(rs); }
private static Recordset ConvertDataGridViewToRecordSet(DataGridView dgv) { Recordset result = new RecordsetClass(); foreach (DataGridViewColumn col in dgv.Columns) { if (col.Visible) { result.Fields._Append(col.HeaderText, GetDataType(col.ValueType), -1, FieldAttributeEnum.adFldIsNullable); } } result.Open(miss, miss, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, -1); foreach (DataGridViewRow row in dgv.Rows) { result.AddNew(miss, miss); int i = 0; foreach (DataGridViewColumn col in dgv.Columns) { if (col.Visible) { result.Fields[i].Value = row.Cells[col.Name].Value; i++; } } } return(result); }
//ВНИМАНИЕ!!!: Сделано только для таблицы vars! private void FillDataRestruct(ConnectionClass conn, string tableName, jrows[] rows) { ArrayList al = new ArrayList(); RecordsetClass rs = new RecordsetClass(); try { int adCmdTable = 2; rs.Open(tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable); if (rs.RecordCount != 0) { rs.MoveFirst(); while (!rs.EOF) { al.Add(rs.Fields[0].Value); rs.MoveNext(); } } } finally { rs.Close(); } int adCmdText = 1; object recCount = new object(); for (int i = 0; i < rows.Length; ++i) { jcolvalue[] cols = rows[i].jcolvalues; StringBuilder sbCol = new StringBuilder(); StringBuilder sbVal = new StringBuilder(); bool insert = true; for (int j = 0; j < cols.Length; ++j) { if (cols[j].name == "name" && al.Contains(cols[j].colvalue)) { insert = false; break; } sbCol.Append(cols[j].name); sbVal.Append('"' + cols[j].colvalue + '"'); if (j != (cols.Length - 1)) { sbCol.Append(","); sbVal.Append(","); } } if (insert) { string strSQL = String.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, sbCol.ToString(), sbVal.ToString()); //System.Windows.Forms.MessageBox.Show(strSQL); conn.Execute(strSQL, out recCount, adCmdText); } } }
private static Recordset GetRecordSet(Connection connection, string sql) { //create new RecordSet ADODB.RecordsetClass rset = new RecordsetClass(); //open it rset.Open(sql, connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0); //return it return(rset); }
public static DateTime GetLongDate() { RecordsetClass Rs = new RecordsetClass(); Rs.Open("Select GetDate() as Date", ConnexionSQL.Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, 0); System.IFormatProvider format = new System.Globalization.CultureInfo("fr-FR", true); DateTime D = new DateTime(); D = DateTime.Parse(Rs.Fields["Date"].Value.ToString(), format, System.Globalization.DateTimeStyles.None); return(D); }
public static RecordsetClass XmlToRecordset(string xml) { var stream = new StreamClass(); stream.Open(Missing.Value, ConnectModeEnum.adModeUnknown, StreamOpenOptionsEnum.adOpenStreamUnspecified, null, null); stream.WriteText(xml, 0); stream.Position = 0; var recordset = new RecordsetClass(); recordset.Open(stream, Missing.Value, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, 0); return(recordset); }
private void cmdOK_Click(object sender, EventArgs e) { Recordset recordset = new RecordsetClass(); recordset.Open("select * from RUn_ID", this.frmMain.DatabaseConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockPessimistic, -1); recordset.MoveNext(); int num2 = this.chkSimulations.Items.Count - 1; int index = 0; while (true) { int num3 = num2; if (index > num3) { this.Close(); return; } bool flag = this.chkSimulations.GetItemCheckState(index) == CheckState.Checked; recordset[]["batch"][] = !flag ? 0 : -1;
private void mainForm_Load(object sender, System.EventArgs e) { // First make use of an ADO Connection type. ConnectionClass cn = new ConnectionClass(); cn.Open("Provider=SQLOLEDB.1;data source=.;initial catalog=pubs;", "sa", "", -1); // Now make use of an ADO Recordset. RecordsetClass rs = new RecordsetClass(); rs.Open("Authors", cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, -1); // Using the recordset, construct a DataTable // which will be bound to the DataGrid widget. DataTable theTable = new DataTable(); // Fill in column names. for (int i = 0; i < rs.Fields.Count; i++) { theTable.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string))); } // Fill in rows. while (!rs.EOF) { DataRow currRow; currRow = theTable.NewRow(); for (int i = 0; i < rs.Fields.Count; i++) { currRow[i] = rs.Fields[i].Value.ToString(); } theTable.Rows.Add(currRow); rs.MoveNext(); } // Now bind to the DataGrid. theDataGrid.DataSource = theTable; // Close up ADO. rs.Close(); cn.Close(); }
public DataTable ExecuteSql(string sql, int samples) { Connection target = new ConnectionClass(); target.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, target, new object[] { this.connectionString }); if ((this.defaultDatabase != null) && (this.defaultDatabase != "")) { target.DefaultDatabase = this.defaultDatabase; } Recordset rs = new RecordsetClass(); rs.Open(sql, target, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, -1); rs.PageSize = samples; DataTable table = ConvertRecordset(rs); rs.Close(); target.Close(); return(table); }
//makes and returns ado recordset _Recordset rs_open(string statement, bool disconnect) { this.check(); statement = statement.ToLower(); RecordsetClass result = new RecordsetClass(); try { result.CursorLocation = CursorLocationEnum.adUseClient; this.connection_.Open(this.connection_string, null, null, 0); this.connection_.BeginTrans(); result.Open( statement, this.connection_string, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockBatchOptimistic, (int)CommandTypeEnum.adCmdText ); //if true, disconnect the recordset if (disconnect) { System.Object notcon = null; result.ActiveConnection = notcon; } } catch (System.Exception e) { if (e != null) { this.connection_.RollbackTrans(); this.connection_.Close(); throw new Exception(e.Message); } } finally { this.connection_.CommitTrans(); this.connection_.Close(); } return(result); }
//Missing的命名空间using System.Reflection; public Recordset ConvertDataTableToRecordset(DataTable table) { Recordset rs = new RecordsetClass(); foreach (DataColumn dc in table.Columns) { rs.Fields._Append(dc.ColumnName, GetDataType(dc.DataType), -1, FieldAttributeEnum.adFldIsNullable); } rs.Open(Missing.Value, Missing.Value, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, -1); foreach (DataRow dr in table.Rows) { rs.AddNew(Missing.Value, Missing.Value); object o; for (int i = 0; i < table.Columns.Count; i++) { rs.Fields[i].Value = dr[i]; o = rs.Fields[i].Value; } } return(rs); }
private static Recordset ConvertDataTableToRecordset(DataTable table) { Recordset result = new RecordsetClass(); foreach (DataColumn col in table.Columns) { result.Fields._Append(col.ColumnName, GetDataType(col.DataType), -1, FieldAttributeEnum.adFldIsNullable); } result.Open(Missing.Value, Missing.Value, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, -1); foreach (DataRow row in table.Rows) { result.AddNew(Missing.Value, Missing.Value); for (int i = 0; i < table.Columns.Count; i++) { result.Fields[i].Value = row[i]; } } return(result); }
private static void PrepareParameterArrays(ref CfrmMain frmMain) { string str = ""; string str2 = ""; Recordset rs = new RecordsetClass(); Recordset recordset2 = new RecordsetClass(); CfrmMain main = frmMain; rs.Open("select * from ParameterInfo order by [Order]", frmMain.DatabaseConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockPessimistic, -1); nparameter_names = (short)ReadSaveFunctions.RecordCount(ref rs); while (true) { int num6; bool flag = !rs.EOF; if (!flag) { rs.MoveFirst(); GlobalVariables.pname = new string[nparameter_names + 1]; GlobalVariables.FirstPos = new short[(nparameter_names + 1) + 1]; P = new float[nparameters + 1]; pgroup = new short[nparameter_names + 1]; plength = new short[nparameter_names + 1]; GlobalVariables.FirstPos[1] = 1; i = 1; short num8 = (short)ReadSaveFunctions.RecordCount(ref rs); i = 1; while (true) { short num4; short num14 = num8; if (i > num14) { GlobalVariables.FirstPos[i] = (short)(nparameters + 1); main = null; return; } GlobalVariables.pname[i] = Conversions.ToString(rs[]["ParameterName"][]);
//Заполняем m_schema из БД public void FillFromADOX( string pathDB, bool withData) { ConnectionClass conn = null; try { conn = new ConnectionClass(); conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathDB,"","",0); m_catalogADOX.ActiveConnection = conn; m_schema.tables = new jtables[m_catalogADOX.Tables.Count]; for(int i = 0; i < m_catalogADOX.Tables.Count; i++) { if (m_catalogADOX.Tables[i].Name.Substring(0,4) != "MSys") { jtables table = new jtables(); table.name = m_catalogADOX.Tables[i].Name; table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count]; for(int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++) { jcolumns column = new jcolumns(); column.name = m_catalogADOX.Tables[i].Columns[j].Name; column.type = m_catalogADOX.Tables[i].Columns[j].Type; column.precision = m_catalogADOX.Tables[i].Columns[j].Precision; column.definedSize = m_catalogADOX.Tables[i].Columns[j].DefinedSize; column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value; column.nullable = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value; column.fixedLength = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value; //tab.columns.SetValue(col, j); table.columns[j] = column; } ArrayList arrIndex = new ArrayList(); //index table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count]; for(int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++) { if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey") { string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name; if ( arrIndex.Contains(nameIndex)) continue; jindexs index = new jindexs(); //index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name; index.clustered = m_catalogADOX.Tables[i].Indexes[j].Clustered; index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey; index.unique = m_catalogADOX.Tables[i].Indexes[j].Unique; index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls; //tab.indexs.SetValue(ind, j); table.indexs[j] = index; arrIndex.Add( index.name); } } //keys table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count]; for(int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++) { if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey") { jkeys key = new jkeys(); key.name = m_catalogADOX.Tables[i].Keys[j].Name; key.column = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name; key.type = m_catalogADOX.Tables[i].Keys[j].Type; table.keys[j] = key; } } //data string tableName = m_catalogADOX.Tables[i].Name; if (withData && tableName == "vars") { RecordsetClass rs = new RecordsetClass(); try { //int adCmdText = 1; int adCmdTable = 2; rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable); if (rs.RecordCount != 0) { table.rows = new jrows[rs.RecordCount]; int rc = 0; rs.MoveFirst(); while (!rs.EOF) { jrows row = new jrows(); row.jcolvalues = new jcolvalue[rs.Fields.Count]; for (int c = 0; c < rs.Fields.Count; c++) { jcolvalue colvalue = new jcolvalue(); colvalue.name = rs.Fields[c].Name; colvalue.colvalue = Convert.ToString(rs.Fields[c].Value); row.jcolvalues[c] = colvalue; } table.rows[rc] = row; rc++; rs.MoveNext(); } } } finally { rs.Close(); } //version try { int adCmdText = 1; rs.Open( "SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText); if (rs.RecordCount != 0) { rs.MoveFirst(); while (!rs.EOF) { m_schema.version = Convert.ToInt32(rs.Fields[0].Value); rs.MoveNext(); } } } finally { rs.Close(); } } //m_schema.tables.SetValue(table, i); m_schema.tables[i] = table; } } m_isSchema = true; } finally { if (conn != null) conn.Close(); m_catalogADOX.ActiveConnection = null; } }
private static Recordset GetRecordSet(Connection connection, string sql) { //create new RecordSet ADODB.RecordsetClass rset = new RecordsetClass(); //open it rset.Open(sql, connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0); //return it return rset; }
//Заполняем m_schema из БД public void FillFromADOX(string pathDB, bool withData) { ConnectionClass conn = null; try { conn = new ConnectionClass(); conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB, "", "", 0); m_catalogADOX.ActiveConnection = conn; m_schema.tables = new jtables[m_catalogADOX.Tables.Count]; for (int i = 0; i < m_catalogADOX.Tables.Count; i++) { if (m_catalogADOX.Tables[i].Name.Substring(0, 4) != "MSys") { jtables table = new jtables(); table.name = m_catalogADOX.Tables[i].Name; table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count]; for (int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++) { jcolumns column = new jcolumns(); column.name = m_catalogADOX.Tables[i].Columns[j].Name; column.type = m_catalogADOX.Tables[i].Columns[j].Type; column.precision = m_catalogADOX.Tables[i].Columns[j].Precision; column.definedSize = m_catalogADOX.Tables[i].Columns[j].DefinedSize; column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value; column.nullable = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value; column.fixedLength = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value; //tab.columns.SetValue(col, j); table.columns[j] = column; } ArrayList arrIndex = new ArrayList(); //index table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count]; for (int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++) { if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey") { string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name; if (arrIndex.Contains(nameIndex)) { continue; } jindexs index = new jindexs(); //index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name; index.clustered = m_catalogADOX.Tables[i].Indexes[j].Clustered; index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey; index.unique = m_catalogADOX.Tables[i].Indexes[j].Unique; index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls; //tab.indexs.SetValue(ind, j); table.indexs[j] = index; arrIndex.Add(index.name); } } //keys table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count]; for (int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++) { if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey") { jkeys key = new jkeys(); key.name = m_catalogADOX.Tables[i].Keys[j].Name; key.column = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name; key.type = m_catalogADOX.Tables[i].Keys[j].Type; table.keys[j] = key; } } //data string tableName = m_catalogADOX.Tables[i].Name; if (withData && tableName == "vars") { RecordsetClass rs = new RecordsetClass(); try { //int adCmdText = 1; int adCmdTable = 2; rs.Open(tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable); if (rs.RecordCount != 0) { table.rows = new jrows[rs.RecordCount]; int rc = 0; rs.MoveFirst(); while (!rs.EOF) { jrows row = new jrows(); row.jcolvalues = new jcolvalue[rs.Fields.Count]; for (int c = 0; c < rs.Fields.Count; c++) { jcolvalue colvalue = new jcolvalue(); colvalue.name = rs.Fields[c].Name; colvalue.colvalue = Convert.ToString(rs.Fields[c].Value); row.jcolvalues[c] = colvalue; } table.rows[rc] = row; rc++; rs.MoveNext(); } } } finally { rs.Close(); } //version try { int adCmdText = 1; rs.Open("SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText); if (rs.RecordCount != 0) { rs.MoveFirst(); while (!rs.EOF) { m_schema.version = Convert.ToInt32(rs.Fields[0].Value); rs.MoveNext(); } } } finally { rs.Close(); } } //m_schema.tables.SetValue(table, i); m_schema.tables[i] = table; } } m_isSchema = true; } finally { if (conn != null) { conn.Close(); } m_catalogADOX.ActiveConnection = null; } }
//ВНИМАНИЕ!!!: Сделано только для таблицы vars! private void FillDataRestruct( ConnectionClass conn, string tableName, jrows[] rows) { ArrayList al = new ArrayList(); RecordsetClass rs = new RecordsetClass(); try { int adCmdTable = 2; rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable); if (rs.RecordCount != 0) { rs.MoveFirst(); while (!rs.EOF) { al.Add(rs.Fields[0].Value); rs.MoveNext(); } } } finally { rs.Close(); } int adCmdText = 1; object recCount = new object(); for ( int i = 0; i < rows.Length; ++i ) { jcolvalue[] cols = rows[i].jcolvalues; StringBuilder sbCol = new StringBuilder(); StringBuilder sbVal = new StringBuilder(); bool insert = true; for ( int j = 0; j < cols.Length; ++j ) { if (cols[j].name == "name" && al.Contains(cols[j].colvalue)) { insert = false; break; } sbCol.Append(cols[j].name); sbVal.Append('"'+cols[j].colvalue+'"'); if (j != (cols.Length-1)) { sbCol.Append(","); sbVal.Append(","); } } if (insert) { string strSQL = String.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, sbCol.ToString(), sbVal.ToString()); //System.Windows.Forms.MessageBox.Show(strSQL); conn.Execute( strSQL, out recCount, adCmdText); } } }
private void mainForm_Load(object sender, System.EventArgs e) { // First make use of an ADO Connection type. ConnectionClass cn = new ConnectionClass(); cn.Open("Provider=SQLOLEDB.1;data source=.;initial catalog=pubs;", "sa", "", -1); // Now make use of an ADO Recordset. RecordsetClass rs = new RecordsetClass(); rs.Open("Authors", cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, -1); // Using the recordset, construct a DataTable // which will be bound to the DataGrid widget. DataTable theTable = new DataTable(); // Fill in column names. for(int i = 0; i < rs.Fields.Count; i++) theTable.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string))); // Fill in rows. while(!rs.EOF) { DataRow currRow; currRow = theTable.NewRow(); for(int i = 0; i < rs.Fields.Count; i++) currRow[i] = rs.Fields[i].Value.ToString(); theTable.Rows.Add(currRow); rs.MoveNext(); } // Now bind to the DataGrid. theDataGrid.DataSource = theTable; // Close up ADO. rs.Close(); cn.Close(); }