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