Monday 28 July 2008

WatiN - Simple SQL Server Class

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:

Richard Allen said...

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

WatINtheWorld said...

Thanks Rich, I might even look @ VLinq (Visual LINQ Query Builder) -
http://code.msdn.microsoft.com/vlinq/Release/ProjectReleases.aspx?ReleaseId=810