Run a SQL Query

[=?Utf-8?B?Q2hyaXM=?=]

How can I run this query against a table in my Access database? I don't know
hwo to use it in C#. In VB I would use .Recordset = "some sql statement". How
do I do this in C#?

//I get a vlaue form a cell and apply it to the SQL statement
commandCol = scriptDataGridView[4, rowNum].FormattedValue.ToString();
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName = "
+ commandCol;
//This reutne a valie SQL statement which will return the Command String in
the database table but how?

Thanks

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Chris" <Ch***@discussions.microsoft.comwrote in message
news:60**********************************@microsof t.com...

How can I run this query against a table in my Access database? I don't
know
hwo to use it in C#. In VB I would use .Recordset = "some sql statement".
How
do I do this in C#?

//I get a vlaue form a cell and apply it to the SQL statement
commandCol = scriptDataGridView[4, rowNum].FormattedValue.ToString();
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
"
+ commandCol;
//This reutne a valie SQL statement which will return the Command String
in
the database table but how?

http://www.csharp-station.com/Tutori.../Lesson01.aspx

--
Ignacio Machin
machin AT laceupsolutions com

[j1mb0jay]

Below class with populate dataset with "whole" database, includes
GetRow(tablename,query); hope this helps.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace SQL
{
public static class Data
{

#region Population
public static void NewSQLConnection()
{
string datasource = ApplicationSettings.MeetySettings.DNS + "\\"
+ ApplicationSettings.MeetySettings.Instance;
string connectionString = "user id=" +
ApplicationSettings.MeetySettings.Username +
"; pwd=" + ApplicationSettings.MeetySettings.Password +
"; Initial Catalog=" +
ApplicationSettings.MeetySettings.TableName +
"; Data Source=" + datasource +
";Packet Size=4096" +
";Connection Timeout=5;";

ApplicationSettings.MeetySettings.SQLConnection = new
SqlConnection(connectionString);
ApplicationSettings.MeetySettings.SQLConnection.St atisticsEnabled
= true;
}

public static string PopulateDataSet()
{
try
{
ApplicationSettings.MeetySettings.SQLConnection.Op en();
ApplicationSettings.MeetySettings.HashTable = new
System.Collections.Hashtable();

ApplicationSettings.MeetySettings.DataSet = new
DataSet("System");
SqlDataAdapter adapter = null;
string query = "SELECT TABLE_NAME as 'Table' from
INFORMATION_SCHEMA.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME !=
'dtproperties' ORDER BY TABLE_NAME ASC";

adapter = new SqlDataAdapter();
adapter.MissingSchemaAction =
System.Data.MissingSchemaAction.AddWithKey;
adapter.SelectCommand = new SqlCommand(query,
ApplicationSettings.MeetySettings.SQLConnection);

adapter.Fill(ApplicationSettings.MeetySettings.Dat aSet,
"Tables");
query = "SELECT KCU1.CONSTRAINT_NAME AS 'FK',
KCU1.TABLE_NAME AS 'FK_Table', KCU1.COLUMN_NAME AS 'FK_Column',
KCU2.CONSTRAINT_NAME AS 'PK', KCU2.TABLE_NAME AS 'PK_Table',
KCU2.COLUMN_NAME AS 'PK_Column' FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG =
RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
ORDER BY KCU1.TABLE_NAME";

adapter = new SqlDataAdapter();
adapter.MissingSchemaAction =
System.Data.MissingSchemaAction.AddWithKey;
adapter.SelectCommand = new SqlCommand(query,
ApplicationSettings.MeetySettings.SQLConnection);

adapter.Fill(ApplicationSettings.MeetySettings.Dat aSet,
"Relations");
query = "SELECT TC.TABLE_NAME as 'PK_Table', CSU.COLUMN_NAME
as 'PK_Column', CSU.CONSTRAINT_NAME as 'PK_Name' FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CSU JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON CSU.CONSTRAINT_NAME =
TC.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'";

adapter = new SqlDataAdapter();
adapter.MissingSchemaAction =
System.Data.MissingSchemaAction.AddWithKey;
adapter.SelectCommand = new SqlCommand(query,
ApplicationSettings.MeetySettings.SQLConnection);

adapter.Fill(ApplicationSettings.MeetySettings.Dat aSet,
"Primary");

string name = string.Empty;

foreach (DataRow dr in
ApplicationSettings.MeetySettings.DataSet.Tables["Tables"].Rows)
{
name = dr["Table"].ToString();
query = "SELECT * FROM " + name;
adapter = new SqlDataAdapter();
adapter.MissingSchemaAction =
System.Data.MissingSchemaAction.Add;
adapter.SelectCommand = new SqlCommand(query,
ApplicationSettings.MeetySettings.SQLConnection);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(adapter);
ApplicationSettings.MeetySettings.HashTable.Add(na me,
adapter);
adapter.Fill(ApplicationSettings.MeetySettings.Dat aSet,
name);

DataRow[] ucRows =
ApplicationSettings.MeetySettings.DataSet.Tables["Primary"].Select("PK_Table
= '" + name + "'");
if (ucRows != null && ucRows.Length 0)
{
string pkName = (string)ucRows[0]["PK_Name"];
DataColumn[] cols = new DataColumn[ucRows.Length];
for (int i = 0; i < ucRows.Length; i++)
{
cols[i] =
ApplicationSettings.MeetySettings.DataSet.Tables[name].Columns[ucRows[i]["PK_Column"].ToString()];
}
ApplicationSettings.MeetySettings.DataSet.Tables[name].Constraints.Add(pkName,
cols, true);

}
}

foreach (DataRow dr in
ApplicationSettings.MeetySettings.DataSet.Tables["Relations"].Rows)
{
name = dr["FK"].ToString();
if (name.IndexOf("Archive") >= 0 || name == "Images")
continue;
string pkTable = dr["PK_Table"].ToString();
string pkCol = dr["PK_Column"].ToString();
string fkTable = dr["FK_Table"].ToString();
string fkCol = dr["FK_Column"].ToString();
ApplicationSettings.MeetySettings.DataSet.Relation s.Add(name,
ApplicationSettings.MeetySettings.DataSet.Tables[pkTable].Columns[pkCol],
ApplicationSettings.MeetySettings.DataSet.Tables[fkTable].Columns[fkCol]);

}

ApplicationSettings.MeetySettings.DataSet.Tables.R emove("Tables");
ApplicationSettings.MeetySettings.DataSet.Tables.R emove("Primary");
ApplicationSettings.MeetySettings.DataSet.Tables.R emove("Relations");
}
catch (Exception ex)
{
return "Error connecting to database - " + ex.Message;
}
finally
{
ApplicationSettings.MeetySettings.SQLConnection.Cl ose();
}

return null;
}
#endregion

#region Manipulation
public static int SQLTableUpdate(string tableName)
{
int ret = -1;
SqlDataAdapter sqlAdapter = null;

sqlAdapter =
(SqlDataAdapter)ApplicationSettings.MeetySettings. HashTable[tableName];
try { ret =
sqlAdapter.Update(ApplicationSettings.MeetySetting s.DataSet, tableName); }
catch
{

}

return ret;
}

public static void AddNewRow(string table, DataRow row)
{
ApplicationSettings.MeetySettings.DataSet.Tables[table].Rows.Add(row);
SQLTableUpdate(table);
}

public static DataView GetDV(string table, string filter)
{
DataView dv = null;

dv = new
DataView(ApplicationSettings.MeetySettings.DataSet .Tables[table]);
dv.RowFilter = filter;

return dv;
}
public static System.Data.DataRow GetNewRow(string table)
{
return
ApplicationSettings.MeetySettings.DataSet.Tables[table].NewRow();
}

public static DataRow GetSingleRow(string table, string filter)
{
DataRow[] rows = null;

rows =
ApplicationSettings.MeetySettings.DataSet.Tables[table].Select(filter);
if (rows.Length 0)
return rows[0];
else
return null;
}
public static string GetIncrementedID(DataView dv, string idColumn)
{
return GetIncrementedID(dv.Table, idColumn);
}
public static string GetIncrementedID(string dt, string idColumn)
{
return
GetIncrementedID(ApplicationSettings.MeetySettings .DataSet.Tables[dt],
idColumn);
}
public static string GetIncrementedID(System.Data.DataTable dt,
string idColumn)
{
if (dt.Rows.Count == 0)
{
return GetInitialID(dt.TableName);
}
else
{
string max = (string)dt.Compute("MAX(" + idColumn + ")",
"");
string[] split = max.Split(new char[] { '_' });
int recordCounter = Convert.ToInt32(split[1]);
recordCounter++;

string newID = NumberToString(recordCounter,
split[1].Length);
return split[0] + "_" + newID;
}

}
private static string NumberToString(int number, int places)
{
string num = number.ToString();
string ret = string.Empty;
if (num.Length >= places)
ret = num;
else
{
ret = new string('0', places - num.Length);
ret += num;
}
return ret;
}
private static string NumberToString(int number, string template)
{
string num = number.ToString();
string ret = string.Empty;
if (num.Length >= template.Length)
ret = num;
else
{
ret = new string('0', template.Length - num.Length);
ret += num;
}
return ret;
}
private static string GetInitialID(string tableName)
{
switch (tableName)
{
case "Mail_Queue":
return "JJM_0001";
case "Departments":
return "DEP_0001";
}
return string.Empty;
}
#endregion
}
}

Regards j1mb0jay (UWA)