473,239 Members | 1,464 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Run a SQL Query

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
Feb 14 '07 #1
2 17611
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
Feb 14 '07 #2
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)
Feb 14 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
4
by: Diamondback | last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets...
14
by: Dave Thomas | last post by:
If I have a table set up like this: Name | VARCHAR Email | VARCHAR Age | TINYINT | NULL (Default: NULL) And I want the user to enter his or her name, email, and age - but AGE is optional. ...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.