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 = scriptDataGridV iew[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***@discussi ons.microsoft.c omwrote in message
news:60******** *************** ***********@mic rosoft.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 = scriptDataGridV iew[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(tablenam e,query); hope this helps.

using System;
using System.Collecti ons.Generic;
using System.Text;
using System.Data.Sql Client;
using System.Data;
using System.Collecti ons;

namespace SQL
{
public static class Data
{

#region Population
public static void NewSQLConnectio n()
{
string datasource = ApplicationSett ings.MeetySetti ngs.DNS + "\\"
+ ApplicationSett ings.MeetySetti ngs.Instance;
string connectionStrin g = "user id=" +
ApplicationSett ings.MeetySetti ngs.Username +
"; pwd=" + ApplicationSett ings.MeetySetti ngs.Password +
"; Initial Catalog=" +
ApplicationSett ings.MeetySetti ngs.TableName +
"; Data Source=" + datasource +
";Packet Size=4096" +
";Connectio n Timeout=5;";

ApplicationSett ings.MeetySetti ngs.SQLConnecti on = new
SqlConnection(c onnectionString );
ApplicationSett ings.MeetySetti ngs.SQLConnecti on.StatisticsEn abled
= true;
}

public static string PopulateDataSet ()
{
try
{
ApplicationSett ings.MeetySetti ngs.SQLConnecti on.Open();
ApplicationSett ings.MeetySetti ngs.HashTable = new
System.Collecti ons.Hashtable() ;

ApplicationSett ings.MeetySetti ngs.DataSet = new
DataSet("System ");
SqlDataAdapter adapter = null;
string query = "SELECT TABLE_NAME as 'Table' from
INFORMATION_SCH EMA.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME !=
'dtproperties' ORDER BY TABLE_NAME ASC";

adapter = new SqlDataAdapter( );
adapter.Missing SchemaAction =
System.Data.Mis singSchemaActio n.AddWithKey;
adapter.SelectC ommand = new SqlCommand(quer y,
ApplicationSett ings.MeetySetti ngs.SQLConnecti on);

adapter.Fill(Ap plicationSettin gs.MeetySetting s.DataSet,
"Tables");
query = "SELECT KCU1.CONSTRAINT _NAME AS 'FK',
KCU1.TABLE_NAME AS 'FK_Table', KCU1.COLUMN_NAM E AS 'FK_Column',
KCU2.CONSTRAINT _NAME AS 'PK', KCU2.TABLE_NAME AS 'PK_Table',
KCU2.COLUMN_NAM E AS 'PK_Column' FROM
INFORMATION_SCH EMA.REFERENTIAL _CONSTRAINTS RC JOIN
INFORMATION_SCH EMA.KEY_COLUMN_ USAGE KCU1 ON KCU1.CONSTRAINT _CATALOG =
RC.CONSTRAINT_C ATALOG AND KCU1.CONSTRAINT _SCHEMA = RC.CONSTRAINT_S CHEMA AND
KCU1.CONSTRAINT _NAME = RC.CONSTRAINT_N AME JOIN
INFORMATION_SCH EMA.KEY_COLUMN_ USAGE KCU2 ON KCU2.CONSTRAINT _CATALOG =
RC.UNIQUE_CONST RAINT_CATALOG AND KCU2.CONSTRAINT _SCHEMA =
RC.UNIQUE_CONST RAINT_SCHEMA AND KCU2.CONSTRAINT _NAME =
RC.UNIQUE_CONST RAINT_NAME AND KCU2.ORDINAL_PO SITION = KCU1.ORDINAL_PO SITION
ORDER BY KCU1.TABLE_NAME ";

adapter = new SqlDataAdapter( );
adapter.Missing SchemaAction =
System.Data.Mis singSchemaActio n.AddWithKey;
adapter.SelectC ommand = new SqlCommand(quer y,
ApplicationSett ings.MeetySetti ngs.SQLConnecti on);

adapter.Fill(Ap plicationSettin gs.MeetySetting s.DataSet,
"Relations" );
query = "SELECT TC.TABLE_NAME as 'PK_Table', CSU.COLUMN_NAME
as 'PK_Column', CSU.CONSTRAINT_ NAME as 'PK_Name' FROM
INFORMATION_SCH EMA.CONSTRAINT_ COLUMN_USAGE CSU JOIN
INFORMATION_SCH EMA.TABLE_CONST RAINTS TC ON CSU.CONSTRAINT_ NAME =
TC.CONSTRAINT_N AME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'";

adapter = new SqlDataAdapter( );
adapter.Missing SchemaAction =
System.Data.Mis singSchemaActio n.AddWithKey;
adapter.SelectC ommand = new SqlCommand(quer y,
ApplicationSett ings.MeetySetti ngs.SQLConnecti on);

adapter.Fill(Ap plicationSettin gs.MeetySetting s.DataSet,
"Primary");

string name = string.Empty;

foreach (DataRow dr in
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les["Tables"].Rows)
{
name = dr["Table"].ToString();
query = "SELECT * FROM " + name;
adapter = new SqlDataAdapter( );
adapter.Missing SchemaAction =
System.Data.Mis singSchemaActio n.Add;
adapter.SelectC ommand = new SqlCommand(quer y,
ApplicationSett ings.MeetySetti ngs.SQLConnecti on);
SqlCommandBuild er commandBuilder = new
SqlCommandBuild er(adapter);
ApplicationSett ings.MeetySetti ngs.HashTable.A dd(name,
adapter);
adapter.Fill(Ap plicationSettin gs.MeetySetting s.DataSet,
name);

DataRow[] ucRows =
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les["Primary"].Select("PK_Tab le
= '" + 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] =
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[name].Columns[ucRows[i]["PK_Column"].ToString()];
}
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[name].Constraints.Ad d(pkName,
cols, true);

}
}

foreach (DataRow dr in
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les["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();
ApplicationSett ings.MeetySetti ngs.DataSet.Rel ations.Add(name ,
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[pkTable].Columns[pkCol],
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[fkTable].Columns[fkCol]);

}

ApplicationSett ings.MeetySetti ngs.DataSet.Tab les.Remove("Tab les");
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les.Remove("Pri mary");
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les.Remove("Rel ations");
}
catch (Exception ex)
{
return "Error connecting to database - " + ex.Message;
}
finally
{
ApplicationSett ings.MeetySetti ngs.SQLConnecti on.Close();
}

return null;
}
#endregion

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

sqlAdapter =
(SqlDataAdapter )ApplicationSet tings.MeetySett ings.HashTable[tableName];
try { ret =
sqlAdapter.Upda te(ApplicationS ettings.MeetySe ttings.DataSet, tableName); }
catch
{

}

return ret;
}

public static void AddNewRow(strin g table, DataRow row)
{
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[table].Rows.Add(row);
SQLTableUpdate( table);
}

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

dv = new
DataView(Applic ationSettings.M eetySettings.Da taSet.Tables[table]);
dv.RowFilter = filter;

return dv;
}
public static System.Data.Dat aRow GetNewRow(strin g table)
{
return
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[table].NewRow();
}

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

rows =
ApplicationSett ings.MeetySetti ngs.DataSet.Tab les[table].Select(filter) ;
if (rows.Length 0)
return rows[0];
else
return null;
}
public static string GetIncrementedI D(DataView dv, string idColumn)
{
return GetIncrementedI D(dv.Table, idColumn);
}
public static string GetIncrementedI D(string dt, string idColumn)
{
return
GetIncrementedI D(ApplicationSe ttings.MeetySet tings.DataSet.T ables[dt],
idColumn);
}
public static string GetIncrementedI D(System.Data.D ataTable dt,
string idColumn)
{
if (dt.Rows.Count == 0)
{
return GetInitialID(dt .TableName);
}
else
{
string max = (string)dt.Comp ute("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(st ring tableName)
{
switch (tableName)
{
case "Mail_Queue ":
return "JJM_0001";
case "Department s":
return "DEP_0001";
}
return string.Empty;
}
#endregion
}
}

Regards j1mb0jay (UWA)