public DataTable GetSchemaTable(string sqlstr, params IDbDataParameter[] args) { error = null; using (OleDbConnection myConn = new OleDbConnection(this.ConnStr)) { OleDbCommand myCmd = new OleDbCommand(sqlstr, myConn); myCmd.CommandTimeout = CommandTimeout; try { myConn.Open(); foreach (IDbDataParameter arg in args) { if (myCmd.CommandType != CommandType.StoredProcedure) { myCmd.CommandType = CommandType.StoredProcedure; } myCmd.Parameters.Add(arg); } OleDbDataReader myReader = myCmd.ExecuteReader(CommandBehavior.KeyInfo); return(myReader.GetSchemaTable()); } catch (OleDbException ex) { error = ex; return(null); } finally { myConn.Close(); myCmd.Dispose(); myConn.Dispose(); } } }
private static IEnumerable <string> GetHeaderValues(OleDbDataReader dataReader) { foreach (DataRow dataRow in dataReader.GetSchemaTable().Rows) { yield return(dataRow.ItemArray[0].ToString()); } }
private bool SqlToMySQL(OleDbDataReader reader, String table_name) { DataTable dataColumn = new DataTable(); DataTable dataTable = new DataTable(); MySqlCommand command = mDB.CreateCommand(); string ErrStr; object id; try { if (!ExecuteScalarMySql(String.Format("DELETE FROM {0};", table_name), out id, out ErrStr)) { nRows = -1; log(String.Format("Ошибка при чистке таблицы {1} = {0}", ErrStr, table_name)); return(false); } //dataTable.Load(reader); //DataTable dt = reader.GetSchemaTable(); int c = reader.FieldCount;//dt.Rows.Count; string[] mas = new string[c]; for (int i = 0; i <= c - 1; i++) { mas[i] = reader.GetName(i);//dt.Rows[i][0].ToString(); } DataTable dt = reader.GetSchemaTable(); foreach (DataRow row in dt.Rows) { string colName = row.Field <string>("ColumnName"); Type t = row.Field <Type>("DataType"); dataTable.Columns.Add(colName, t); } while (reader.Read()) { var newRow = dataTable.Rows.Add(); foreach (DataColumn col in dataTable.Columns) { newRow[col.ColumnName] = reader[col.ColumnName]; } } int d = 0; if (String.Compare(table_name, "items", true) == 0) { d = 2; } BulkInsert(dataTable.Select(), mas, table_name, command, d); log(String.Format("Добавили в {0} {1} строк", table_name, dataTable.Rows.Count)); } catch (Exception e) { ErrStr = e.Message.Trim(); //Console.WriteLine(ErrStr); log(String.Format("Ошибка при записи в таблицу {0} : {1}", table_name, ErrStr)); return(false); } return(true); }
// Methods protected bool CollectResultsInList(ArrayList results, OleDbDataReader resultSet) { int count = results.Count; try { Hashtable hashtable = new Hashtable(); foreach (DataRow row in resultSet.GetSchemaTable().Rows) { hashtable.Add(row["ColumnName"].ToString(), null); } Hashtable hashtable2 = null; while (resultSet.Read()) { hashtable2 = (Hashtable)hashtable.Clone(); foreach (string str in hashtable.Keys) { hashtable2[str] = resultSet[str]; if (hashtable2[str] == null) { hashtable2[str] = ""; } } results.Add(hashtable2); } } catch (Exception exception) { this.error.message = "An error occurred while collecting the results of your query."; this.error.info = exception.ToString(); } return(results.Count > count); }
private bool GetSourceSchema() { try { using (OleDbConnection conn = new OleDbConnection(sourceConnectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand("select * from " + Table.Table_Name + " where rownum < 0", conn); OleDbDataReader reader = cmd.ExecuteReader(); srcSchemaTable = reader.GetSchemaTable(); reader.Close(); conn.Close(); } } #if !DEBUG catch (Exception ex) { srcSchemaTable = null; return(false); } #endif finally { } return(true); }
/// <summary> /// SqlDataReader转成DataTable实现方法 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataTable DataReaderToDataTable(string strSql) { //string connstr = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString; OleDbConnection OleDbConn = new OleDbConnection(connstr); OleDbCommand OleDbComm = new OleDbCommand(strSql, OleDbConn); OleDbDataReader dataReader = null; DataTable dtReturn = null; object[] value = null; try { OleDbComm.Connection.Open(); dataReader = OleDbComm.ExecuteReader(); if (dataReader.HasRows) { dtReturn = CreateTableBySchemaTable(dataReader.GetSchemaTable()); value = new object[dataReader.FieldCount]; while (dataReader.Read()) { dataReader.GetValues(value); dtReturn.LoadDataRow(value, true); } value = null; } } catch (Exception err) { } finally { dataReader.Close(); OleDbComm.Connection.Close(); OleDbComm.Dispose(); } return(dtReturn); }
private void TablesDropDownClosed(object sender, EventArgs e) { dataGridView.Columns.Clear(); using (OleDbCommand command = new OleDbCommand($"SELECT * FROM {comboBoxTables.Text} WHERE 1 = 0", connection)) { OleDbDataReader reader = null; DataTable table = null; try { reader = command.ExecuteReader(); table = reader.GetSchemaTable(); } catch { return; } foreach (DataRow row in table.Rows) { string columnName = row.Field <string>("ColumnName"); Type columnDT = row.Field <Type>("DataType"); dataGridView.Columns.Add(columnName.ToLower(), columnName); columnsNames.Add(columnName); columnsDateType.Add(columnDT.Name); } } DataLoad($"SELECT * FROM {comboBoxTables.Text};"); }
/// <summary> /// 执行查询存储过程返回结果集 /// </summary> /// <param name="TransID">连接字符串</param> /// <param name="StrConn">连接字符串</param> /// <param name="ProcedureName">存储过程名</param> /// <param name="Params">Sql 参数,参数值所在位置必须与参数名一致</param> /// <returns>返回数据集</returns> public DataTable QueryProcedureTable(string TransID, string StrConn, string ProcedureName, Dictionary <string, object> Params) { OleDbConnection oConn = GetOleDbConnection(TransID, StrConn); try { using (OleDbCommand oCmd = new OleDbCommand(ProcedureName, oConn)) { oCmd.CommandType = CommandType.StoredProcedure; CreateSqlProcedureParam(oCmd, Params); using (OleDbDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)) { using (DataTable dtSchema = odr.GetSchemaTable()) { DataTable dtResult = BuildDataTable(dtSchema); object[] aryValues = new object[odr.FieldCount]; dtResult.BeginLoadData(); while (odr.Read()) { odr.GetValues(aryValues); dtResult.LoadDataRow(aryValues, true); } dtResult.EndLoadData(); aryValues = null; return(dtResult); } } } } finally { CloseConnection(TransID, oConn); } }
private void viewFeedback() { string path = Directory.GetCurrentDirectory(); string connstring = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + path + @"\GameMuseumManagementSystem.accdb"; // Connect to database OleDbConnection conn = new OleDbConnection(connstring); conn.Open(); // Our query on database OleDbCommand feedback_cmd = new OleDbCommand("SELECT * FROM " + feedbacklist.SelectedItem, conn); question_list_view.Columns.Add("Question", 200); question_list_view.Columns.Add("Answer", 200); question_list_view.View = View.Details; // Execute select query and display feedback's questions OleDbDataReader feedback_reader = feedback_cmd.ExecuteReader(CommandBehavior.SchemaOnly); var table = feedback_reader.GetSchemaTable(); var nameCol = table.Columns["ColumnName"]; foreach (DataRow row in table.Rows) { question_list_view.Items.Add(new ListViewItem(new String[] { row[nameCol].ToString() })); } // TODO: add input for answer }
/// <summary> /// 执行查询SQL语句 /// </summary> /// <param name="StrConn">连接字符串</param> /// <param name="StrConn">连接字符串</param> /// <param name="TransID">事务ID</param> /// <param name="SqlText">Sql 文本</param> /// <param name="SqlParams">Sql 参数,参数值所在位置必须与参数名一致</param> /// <returns>返回数据集</returns> public DataTable QueryTable(string TransID, string StrConn, string SqlText, object[] SqlParams) { OleDbConnection oConn = GetOleDbConnection(TransID, StrConn); try { using (OleDbCommand oCmd = new OleDbCommand(SqlText, oConn)) { oCmd.Transaction = GetTransaction(TransID); CreateSqlParam(oCmd, SqlParams); using (OleDbDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)) { using (DataTable dtSchema = odr.GetSchemaTable()) { DataTable dtResult = BuildDataTable(dtSchema); object[] aryValues = new object[odr.FieldCount]; dtResult.BeginLoadData(); while (odr.Read()) { odr.GetValues(aryValues); dtResult.LoadDataRow(aryValues, true); } dtResult.EndLoadData(); aryValues = null; return(dtResult); } } } } finally { CloseConnection(TransID, oConn); } }
/// <summary> /// 执行查询存储过程返回结果集 /// </summary> /// <param name="TransID">连接字符串</param> /// <param name="StrConn">连接字符串</param> /// <param name="ProcedureName">存储过程名</param> /// <param name="Params">Sql 参数,参数值所在位置必须与参数名一致</param> /// <returns>返回数据集</returns> public byte[] QueryProcedure(string TransID, string StrConn, string ProcedureName, Dictionary <string, object> Params) { OleDbConnection oConn = GetOleDbConnection(TransID, StrConn); try { using (OleDbCommand oCmd = new OleDbCommand(ProcedureName, oConn)) { oCmd.CommandType = CommandType.StoredProcedure; CreateSqlProcedureParam(oCmd, Params); using (OleDbDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)) { using (DataTable dtSchema = odr.GetSchemaTable()) { using (CJia.Net.Serialization.SerializationWriter sw = new Net.Serialization.SerializationWriter()) { SerializationSchema(sw, dtSchema); object[] aryValues = new object[odr.FieldCount]; while (odr.Read()) { odr.GetValues(aryValues); sw.WriteOptimized(aryValues); } aryValues = null; return(sw.ToArray()); } } } } } finally { CloseConnection(TransID, oConn); } }
public static bool ColumnExists(string ColumnName, string TableName, string ConnectionString) { //string SQL = "select count(*) from sysColumns where [name] = '" + ColumnName + "' and [id] = object_id('" + TableName + "')"; //OleDbConnection connection = new OleDbConnection(ConnectionString); //OleDbCommand command = new OleDbCommand(SQL, connection); //connection.Open(); //bool Exists = Convert.ToBoolean(command.ExecuteScalar()); //connection.Close(); //return Exists; OleDbConnection connection = new OleDbConnection(ConnectionString); OleDbCommand cmd = new OleDbCommand("select * from " + TableName, connection); connection.Open(); OleDbDataReader DataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable Schema = DataReader.GetSchemaTable(); connection.Close(); return(Schema.Select("ColumnName = '" + ColumnName + "'").Length == 1); }
public static List <Row> getObjects(string command) { List <Row> Rows = new List <Row>(); try { ConnectionState(true); OleCommand = new OleDbCommand(command, Connection); Reader = OleCommand.ExecuteReader(); while (Reader.Read()) { Row row = new Row(); var table = Reader.GetSchemaTable(); var nameCol = table.Columns["ColumnName"]; var fields = new List <string>(); foreach (DataRow r in table.Rows) { fields.Add(r[nameCol].ToString()); } for (int i = 0; i < Reader.FieldCount; i++) { row.AddColume(new Col(fields[i], Reader[i].ToString())); } Rows.Add(row); } Reader.Close(); ConnectionState(false); return(Rows); } catch { ConnectionState(false); return(null); } }
void FiltraColunasGrids() { if (dgvTables.SelectedCells.Count == 0) { return; } if (dgvTables.SelectedCells[0].RowIndex > dgvTables.Rows.Count - 1) { return; } if (dgvTables.Rows[dgvTables.SelectedCells[0].RowIndex].Cells[2].Value == null) { return; } string tableName = dgvTables.Rows[dgvTables.SelectedCells[0].RowIndex].Cells[2].Value.ToString(); ((DataTable)dgvIndices.DataSource).DefaultView.RowFilter = "TABLE_NAME = '" + tableName + "'"; dgvIndices.Columns[0].Visible = false; oleDbCommand1.CommandText = "SELECT * FROM [" + tableName + "] WHERE 0=1"; try { OleDbDataReader reader = oleDbCommand1.ExecuteReader(CommandBehavior.KeyInfo); DataTable schema = reader.GetSchemaTable(); dgvCampos.DataSource = schema; reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public List <string> GetTableColumnNames(string tableName) { string command_str = "SELECT * FROM [" + tableName + "]"; using (var connection = new OleDbConnection(connectionStringR)) { OleDbCommand cmd = new OleDbCommand(command_str, connection); try { connection.Open(); } catch (Exception e) { MessageBox.Show(e.Message); } OleDbDataReader rdr = cmd.ExecuteReader(); var table = rdr.GetSchemaTable(); var nameCol = table.Columns["ColumnName"]; //var schemaTable = connection.GetOleDbSchemaTable( // OleDbSchemaGuid.Columns, // new Object[] { null, null, tableName }); //if (schemaTable == null) // return null; List <string> str_list = new List <string>(); foreach (DataRow r in table.Rows) { str_list.Add(r[nameCol].ToString()); } return(str_list); } }
/// <summary> /// 使用SELECT语句查询表中的字段,返回查询字段的列名和.NET数据类型数据类型 /// </summary> /// <param name="select">SELECT语句,用于指定表和查询的字段</param> /// <returns>字段名,.NET数据类型的字典集合</returns> public Dictionary <string, Type> GetTableSchema(string select) { OleDbDataReader reader = this.conn.GetRecord(select); try { var result = new Dictionary <string, Type>(); DataTable schemaTable = reader.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { string colName = row["ColumnName"] as string; Type colType = row["DataType"] as Type; result.Add(colName, colType); } return(result); } catch (OleDbException ex) { string errorMessage = "从数据库SELECT数据集发生异常\n" + "发生错误的SQL语句:" + select; throw new Exception(errorMessage, ex); } finally { reader.Close(); reader.Dispose(); } }
internal override Dictionary <string, DBColumn> GetColumns(string TableName) { Dictionary <string, DBColumn> DBColumns = new Dictionary <string, DBColumn>(); if (conn == null || conn.State != ConnectionState.Open) { OpenDB(); } OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = string.Format("Select * From [{0}]", TableName); OleDbDataReader OleDbdr = cmd.ExecuteReader(CommandBehavior.SingleRow); OleDbdr.Read(); DataTable ColumnList = OleDbdr.GetSchemaTable(); int i = 0; foreach (DataRow dr in ColumnList.Rows) { DBColumn dbc = new DBColumn() { Name = dr["ColumnName"].ToString() }; dbc.Ordinal = (int)dr["ColumnOrdinal"]; dbc.Type = dr["DataType"].ToString(); dbc.SqlType = OleDbdr.GetDataTypeName(dbc.Ordinal); dbc.Size = (int)dr["ColumnSize"]; dbc.NumericPrecision = Convert.ToInt32(dr["NumericPrecision"]); dbc.NumericScale = Convert.ToInt32(dr["NumericScale"]); dbc.IsLong = (bool)dr["IsLong"]; dbc.IsNullable = (bool)dr["AllowDBNull"]; dbc.IsUnique = (bool)dr["IsUnique"]; dbc.IsKey = (bool)dr["IsKey"]; dbc.IsAutoIncrement = (bool)dr["IsAutoIncrement"]; DBColumns.Add(dbc.Name, dbc); i++; } // Limit result to the current table string[] restrictionValues = new string[4]; restrictionValues[2] = TableName; ColumnList = conn.GetSchema("Columns", restrictionValues); foreach (DataRow dr in ColumnList.Rows) { DBColumn dbc = DBColumns[dr["COLUMN_NAME"].ToString()]; dbc.HasDefault = (bool)dr["COLUMN_HASDEFAULT"]; dbc.DefaultValue = dr["COLUMN_DEFAULT"] is DBNull ? string.Empty : dr["COLUMN_DEFAULT"].ToString(); dbc.DatetimePrecision = dr["DATETIME_PRECISION"] is DBNull ? 0 : Convert.ToInt32(dr["DATETIME_PRECISION"]); DBColumns[dr["COLUMN_NAME"].ToString()] = dbc; } CloseDB(); return(DBColumns); }
/// <summary> /// Converts DataReader to DataSet. /// </summary> /// <param name="myReader"></param> /// <param name="tableName"></param> /// <returns></returns> protected DataSet ConvertToDataSet(OleDbDataReader dataReader, string tableName) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = dataReader.GetSchemaTable(); DataTable dataTable = new DataTable(tableName); if (schemaTable != null) { // A query returning records was executed for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow dataRow = schemaTable.Rows[i]; // Create a column name that is unique in the data table string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>"; // Add the column definition to the data table DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]); dataTable.Columns.Add(column); } //Add the data table to the dataset. dataSet.Tables.Add(dataTable); // Fill the data table. while (dataReader.Read()) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < dataReader.FieldCount; i++) { dataRow[i] = dataReader.GetValue(i); } dataTable.Rows.Add(dataRow); } } else { // No records were returned. DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add(dataTable); DataRow dataRow = dataTable.NewRow(); dataRow[0] = dataReader.RecordsAffected; dataTable.Rows.Add(dataRow); } }while (dataReader.NextResult()); return(dataSet); }
/// <summary> /// Fetch schema information on keys. /// </summary> private DataTable GetPrimaryKeyInfo(string tableName) { OleDbConnection conn = provider.GetConnection() as OleDbConnection; OleDbCommand cmd = new OleDbCommand("select * from " + tableName, conn); OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable result = dr.GetSchemaTable(); conn.Close(); return(result); }
private void mostraCampos(string tabela) { OleDbCommand cmd = conOle.CreateCommand(); cmd.CommandText = "SELECT * FROM [" + tabela + "] WHERE 0=1"; OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable tbCampos = dr.GetSchemaTable(); dgvCampos.DataSource = tbCampos; }
public DataTable GetSchemaTable() { if (SDR != null) { return(SDR.GetSchemaTable()); } else { return(ODR.GetSchemaTable()); } }
/// <summary> /// Parse MDB file and return data as a datatable /// </summary> /// <returns></returns> protected DataSet RetrieveFromMDBFile(string source) { DataSet mdbData = new DataSet(); //This might only work on machines where the Access Engine exists AccessDatabaseEngine_x64.exe /passive //https://www.microsoft.com/en-us/download/details.aspx?id=13255 string connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + source + ";"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); List <string> tableNames = new List <string>(); var tables = connection.GetSchema("Tables"); foreach (DataRow row in tables.Rows) { var tableName = row["TABLE_NAME"].ToString(); //Exclude the system tables if (!tableName.StartsWith("MSys")) { tableNames.Add(tableName); } } foreach (var tableName in tableNames) { DataTable data = mdbData.Tables.Add(tableName + "_DATA"); using (OleDbCommand command = new OleDbCommand(string.Empty, connection)) { command.CommandText = "SELECT * FROM " + tableName; using (OleDbDataReader reader = command.ExecuteReader()) { DataTable schema = reader.GetSchemaTable(); schema.TableName = tableName + "_SCHEMA"; mdbData.Tables.Add(schema); data.Load(reader); } } } } catch (Exception ex) { this.Logger.LogError(ex, "Errors retrieving data from MDB file"); } } return(mdbData); }
private void buttonSearch_Click(object sender, EventArgs e) { try { listViewResult.Clear(); string indexerConnectionString = "provider=Search.CollatorDSO.1;EXTENDED PROPERTIES='Application=Windows'"; OleDbConnection connection = new OleDbConnection(indexerConnectionString); connection.Open(); // string sql = "SELECT System.ItemName, System.Title, System.Size FROM SYSTEMINDEX WHERE System.Size > 1024"; OleDbCommand command = connection.CreateCommand(); command.CommandText = GetSql(textBoxQuery.Text); OleDbDataReader reader = command.ExecuteReader(); DataTable schemaTable = reader.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { listViewResult.Columns.Add(row[0].ToString()); //, 200, HorizontalAlignment.Left); } while (reader.Read()) { ListViewItem item = new ListViewItem(reader[0].ToString()); for (int i = 1; i < reader.FieldCount; i++) { if (reader[i] is string[]) { item.SubItems.Add(String.Join(", ", ((string[])reader[i]))); } else { item.SubItems.Add(reader[i].ToString()); } } ////ListViewItem item = new ListViewItem( //// new string[] {reader[0].ToString(), reader[1].ToString(), reader[2].ToString()}); //ListViewItem item = new ListViewItem(reader[0].ToString()); //item.SubItems.Add(reader[1].ToString()); //item.SubItems.Add(reader[2].ToString()); listViewResult.Items.Add(item); // listView1.Items.Add(item); // Console.WriteLine("{0} {1} {2}", reader[0], reader[1], reader[2]); } connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
//通过datareader读取当前页数据,并且封装为dataset返回 public static DataSet Query(string sql, int curPageIndex, int pageSize, int totalRecord) { DataSet ds = new DataSet(); OleDbDataReader dr = AccessDbUtil.ExecuteReader(sql); // DataTable st = dr.GetSchemaTable(); DataTable dt = ds.Tables.Add("ds"); if (st != null) { foreach (DataRow row in st.Rows) { DataColumn c = new DataColumn { ColumnName = row["ColumnName"].ToString(), DataType = System.Type.GetType(row["DataType"].ToString()) }; dt.Columns.Add(c); //Debug.WriteLine(c.ColumnName+"--"+c.DataType); } // int colCount = st.Rows.Count; int count = 0; int start = curPageIndex * pageSize; int end = start + pageSize; while (dr.Read()) { if (count < start) { count++; continue; } DataRow r = dt.NewRow(); for (int i = 0; i < colCount; i++) { r[i] = dr[i]; } dt.Rows.Add(r); count++; if (count >= end) { break; } } } dr.Close(); return(ds); }
public static DataTable SelectCommand(string command)//комманда select { Table = new DataTable(); connection = new OleDbConnection(connectionString); ConnectionOpen(); dbCommand = new OleDbCommand(command, connection); dataReader = dbCommand.ExecuteReader(); if (dataReader.HasRows) { DataTable schemaTable = dataReader.GetSchemaTable(); schemaTable.TableName = command.Split(' ')[3]; foreach (DataRow row in schemaTable.Rows) { string colName = row.Field <string>("ColumnName"); Type t = row.Field <Type>("DataType"); Table.Columns.Add(colName, t); } while (dataReader.Read()) { var newRow = Table.Rows.Add(); foreach (DataColumn col in Table.Columns) { newRow[col.ColumnName] = dataReader[col.ColumnName]; } } Table.TableName = dataReader.GetSchemaTable().TableName; } else { MessageBox.Show("Запрос не вернул строк.", "Предупреждение!"); } dataReader.Close(); ConnectionClose(); return(Table); }
public string generateTableScript(string table) { OleDbDataReader cols = Connection.GetDATAREADER("select top 1 * from [" + table + "]"); DataTable dt = cols.GetSchemaTable(); List <string> list = new List <string>(); foreach (DataRow colItem in dt.Rows) { list.Add("\t" + colItem["columnname"].ToString() + " " + TrimAccessFieldType(colItem["datatype"].ToString())); } return("CREATE TABLE [" + table + "] (\r\n" + string.Join(",\r\n", list.ToArray()) + "\r\n)"); }
public string GetConverterOutput(string sourceDB, string sourceTable, string outputPath) { List <object> cols = new List <object>(); List <string> stringArr = new List <string>(); List <string> scriptValues = new List <string>(); string parsedValues = ""; string scriptString = ""; string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceDB; using (OleDbConnection connection = new OleDbConnection(connString)) { connection.Open(); OleDbDataReader reader = null; OleDbCommand command = new OleDbCommand("SELECT * FROM " + sourceTable, connection); reader = command.ExecuteReader(); var tabName = reader.GetSchemaTable(); var nameCol = tabName.Columns["ColumnName"]; foreach (DataRow row in tabName.Rows) { cols.Add("`" + row[nameCol] + "`"); } var result = string.Join(",", cols); cols.Clear(); scriptString = "INSERT INTO `" + sourceTable + "` (" + result + ") VALUES \n"; while (reader.Read()) { int i = 0 - 1; while (i++ < tabName.Rows.Count - 1) { stringArr.Add("'" + reader.GetValue(i).ToString() + "'"); } var resultRow = string.Join(",", stringArr); scriptValues.Add("(" + resultRow + ")"); parsedValues = string.Join(",\n", scriptValues); stringArr.Clear(); } scriptString += parsedValues; File.WriteAllText(outputPath, scriptString += ";"); string retValue = scriptString += ";"; scriptString = ""; return(retValue); } }
/// <summary> /// 返回一个查询语句执行结果的表结构 /// </summary> /// <param name="sql">查询语句,不支持复杂SQL</param> /// <returns></returns> private static DataTable GetTableSchema(string sql) { sql = sql.ToUpper(); DataTable dt = null; using (OleDbConnection con = GetDBConnection()) { OleDbCommand cmd = new OleDbCommand(sql, con); con.Open(); using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection)) { dt = dr.GetSchemaTable(); } } return(dt); }
/// <summary> /// Collect and display the field information for a selected column name /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void GetFieldInformationToolStripMenuItem_Click(object sender, EventArgs e) { try { using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) { string sSql = string.Empty; if (mTableSelected == true) { sSql = "SELECT [" + lstFields.SelectedItem.ToString().Trim() + "] FROM [" + lstTables.SelectedItem.ToString().Trim() + "]"; } else { sSql = "SELECT [" + lstFields.SelectedItem.ToString().Trim() + "] FROM [" + lstViews.SelectedItem.ToString().Trim() + "]"; } OleDbCommand cmd = new OleDbCommand(sSql, conn); conn.Open(); OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable schemaTable = rdr.GetSchemaTable(); StringBuilder sb = new StringBuilder(); foreach (DataRow myField in schemaTable.Rows) { foreach (DataColumn myProperty in schemaTable.Columns) { sb.Append(myProperty.ColumnName + " = " + myField[myProperty].ToString() + Environment.NewLine); } // report MessageBox.Show(sb.ToString(), "Field Information"); // burn the reader rdr.Close(); // exit return; } } } catch { MessageBox.Show("Unable to attach to this table with current user; check database security permissions.", "Field Information"); } }
private static string[] GetPrimaryKeyColumns(string tableName) { using (OleDbConnection cn = new OleDbConnection(DataSeverConnection.Instance.Connection.ConnectionString)) { using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + tableName + "]", cn)) { cn.Open(); OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); var schemaTable = reader.GetSchemaTable(); var keyQuery = ( from T in schemaTable.AsEnumerable() where T.Field <bool>("IsKey") select T.Field <string>("ColumnName")).ToArray(); return(keyQuery); } } }