For a while, I searched long & hard for a simple Class that would allow my tests to easilly query a SQL Server database. Unfortunately, I couldn't find anything that suited my requirements, and so, I enlisted the help of my Boss, who helped me create the following. I cannot guarantee it's suitability for others, but it certainly works for me.
namespace Common
{
public class SQLServer
{
private SqlConnection _sqlConn;
public SqlConnection myConnection
{
get
{
if (_sqlConn.State != System.Data.ConnectionState.Open)
{
_sqlConn.Open();
}
return _sqlConn;
}
}
public void CONNECT(string uname, string pword, string dbserver, bool isTrustedConnection, string database, int connTimeout)
{
string connStr = string.Empty;
if (isTrustedConnection)
{
connStr = " server=" + dbserver + "; Trusted_Connection=yes" + "; database=" + database + "; connection timeout=" + connTimeout.ToString();
}
else
{
connStr = " user id=" + uname + "; password=" + pword + "; server=" + dbserver + "; database=" + database + "; connection timeout=" + connTimeout.ToString();
}
_sqlConn = new SqlConnection(connStr);
try
{
_sqlConn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Connection string not valid:" + connStr);
}
}
public void DISCONNECT()
{
myConnection.Close();
}
public string SELECT(string tableName, string columnName, string fieldname, string value)
{
string sqlStr = string.Empty;
sqlStr = " SELECT [" + fieldname + "] FROM [" + tableName + "] WHERE [" + columnName + "]= '" + value+"'";
SqlCommand sqlComm = new SqlCommand(sqlStr, myConnection);
object o = sqlComm.ExecuteScalar();
if (o == null)
return string.Empty;
else
return o.ToString();
}
public System.Data.DataTable SelectMultipleValues(string tableName, string columnName, string[] fieldNames, string[] critieraValues)
{
System.Data.DataTable values = null;
string sqlStr = " SELECT ";
foreach(string fieldName in fieldNames)
{
sqlStr+="[" + fieldName + "], ";
}
if (sqlStr.EndsWith(", "))
{
sqlStr = sqlStr.Substring(0, sqlStr.Length - 2);
}
sqlStr += " FROM [" + tableName + "] WHERE [" + columnName + "] IN(";
foreach (string criteriaValue in critieraValues)
{
sqlStr += "'" + criteriaValue + "',";
}
if (sqlStr.EndsWith(","))
{
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
}
sqlStr += ")";
SqlCommand sqlComm = new SqlCommand(sqlStr, myConnection);
SqlDataReader reader = sqlComm.ExecuteReader();
if (reader != null && reader.HasRows)
{
values = GetTable(reader);
reader.Close();
}
return values;
}
public System.Data.DataTable GetTable(System.Data.SqlClient.SqlDataReader _reader)
{
System.Data.DataTable _table = _reader.GetSchemaTable();
System.Data.DataTable _dt = new System.Data.DataTable();
System.Data.DataColumn _dc;
System.Data.DataRow _row;
System.Collections.ArrayList _al = new System.Collections.ArrayList();
for (int i = 0; i < _table.Rows.Count; i++)
{
_dc = new System.Data.DataColumn();
if (!_dt.Columns.Contains(_table.Rows[i]["ColumnName"].ToString()))
{
_dc.ColumnName = _table.Rows[i]["ColumnName"].ToString();
_dc.Unique = Convert.ToBoolean(_table.Rows[i]["IsUnique"]);
_dc.AllowDBNull = Convert.ToBoolean(_table.Rows[i]["AllowDBNull"]);
_dc.ReadOnly = Convert.ToBoolean(_table.Rows[i]["IsReadOnly"]);
_al.Add(_dc.ColumnName);
_dt.Columns.Add(_dc);
}
}
while (_reader.Read())
{
_row = _dt.NewRow();
for (int i = 0; i < _al.Count; i++)
{
_row[((System.String)_al[i])] = _reader[(System.String)_al[i]];
}
_dt.Rows.Add(_row);
}
return _dt;
}
}
}
How do I use the Class?
public static void SelectValues()
{
Common.SQLServer sq = new Common.SQLServer();
sq.CONNECT("sa", "sa", "SQLServerInstance", true, "Database", 10);
string[] fieldNames = { "ColumnValue" };
string[] criteriaValues = { "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8" };
System.Data.DataTable result = sq.SelectMultipleValues("Option", "Name", fieldNames, criteriaValues);
foreach (System.Data.DataRow row in result.Select())
{
foreach (System.Data.DataColumn column in result.Columns)
{
Console.WriteLine((string)row[column]);
}
}
sq.DISCONNECT();
}
2 comments:
I would seriously suggest you take a look at using a LINQ to SQL data context layer instead of this kind of custom class, a little bit of learning up front will save you a whole bunch of time later.
Using LINQ to SQL you could write something like:
using(var ctx = new DataContext())
{
var rows = from aRowValue in ctx.SomeTable
where aRowValue.ColumnValue == "test"
select aRowValue;
foreach(var row in rows)
{
Console.WriteLine(row.ColumnValue.ToString());
}
}
Which IMO looks a look neater and easier to understand what is going on. Don't be scared by the syntax either, once you understand the basics you'll love it. Scott Guthrie has a good introductory article on LINQ to SQL here: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
Cheers
Thanks Rich, I might even look @ VLinq (Visual LINQ Query Builder) -
http://code.msdn.microsoft.com/vlinq/Release/ProjectReleases.aspx?ReleaseId=810
Post a Comment