Run a SQL Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi,

Chris said:
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?
Click to expand...

www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx
 
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.StatisticsEnabled
= true;
}

public static string PopulateDataSet()
{
try
{
ApplicationSettings.MeetySettings.SQLConnection.Open();
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.DataSet,
"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.DataSet,
"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.DataSet,
"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(name,
adapter);
adapter.Fill(ApplicationSettings.MeetySettings.DataSet,
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 =
ApplicationSettings.MeetySettings.DataSet.Tables[name].Columns[ucRows["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.Relations.Add(name,
ApplicationSettings.MeetySettings.DataSet.Tables[pkTable].Columns[pkCol],
ApplicationSettings.MeetySettings.DataSet.Tables[fkTable].Columns[fkCol]);

}

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

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.MeetySettings.DataSet, tableName); }
catch
{

}

return ret;
}

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

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

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

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

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

rows =
ApplicationSettings.MeetySettings.DataSet.Tables
.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)
 
Back
Top