/// <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);
}
//ВНИМАНИЕ!!!: Сделано только для таблицы 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);
}
}
}
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);
}
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();
}
//Заполняем 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 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();
}
//ВНИМАНИЕ!!!: Сделано только для таблицы 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);
}
}
}
//Заполняем 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;
}
}