Many times we need to build a
sql query using concating strings according to situation, quite often than not we
go with the flow and create function each time to suit our current need. Then
why not create a class that will be useful in most of this situation can
further augmented to as new requirement pops in that can’t be fulfilled by our
current class. Bellow is class that I build keeping in this
mind I will like to share with you.
Code snipet
DBManager dbManager = new
DBManager();
dbManager.ConnectionString =
ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
try
{
dbManager.Open();
AffectedRows =
dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
return (AffectedRows > 0) ?
true : false;
}
catch (Exception ex)
{
}
finally
{
dbManager.Close();
}
return false;
}
in each of this method uses my data access
layer that can be modified to suit your requirement.
This class basically deals
with SELECT; INSERT and UPDATE SQL statements modus operandi is to build a
valid sql statement using input parameters
is either string or string
array.
Here is My Class
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Text;
using Conflux.DLL;
using System.Configuration;
using System.Data;
namespace Conflux.Helper
{
public class MssqlQueryBuilder
{
public static bool Update(string
TableName, string[] ColoumNames, string[] ColoumValues,
string[] WhereKey,
string[] WhereValue)
{
string ExcludeQuotes=string.Empty;
StringBuilder Query = new
StringBuilder("update ");
int AffectedRows = int.MinValue;
if (ColoumNames.Length !=
ColoumValues.Length)
{
return false;
}
if (WhereKey.Length !=
WhereValue.Length)
{
return false;
}
Query.Append(TableName);
Query.Append(" set ");
for (int i = 0; i <
ColoumNames.Length; i++)
{
Query.Append(ColoumNames[i]);
Query.Append(" = ");
Query.Append("'");
ExcludeQuotes =
ColoumValues[i];
if (ColoumValues[i] !=
"")
{
if
((ColoumValues[i].Substring(0, 1) == "'") &&
(ColoumValues[i].Substring(ColoumValues[i].Length
- 1, 1) == "'"))
{
ExcludeQuotes =
ColoumValues[i].Substring(1, ColoumValues[i].Length - 2);
}
}
Query.Append(ExcludeQuotes);
Query.Append("'");
if (i != ColoumNames.Length -
1)
{
Query.Append(" ,
");
}
}
Query.Append(" where ");
ExcludeQuotes = string.Empty;
for (int i = 0; i <
WhereKey.Length; i++)
{
Query.Append(WhereKey[i]);
Query.Append(" = ");
Query.Append("'");
ExcludeQuotes = WhereValue[i];
if (WhereValue[i] !=
"")
{
if
((WhereValue[i].Substring(0, 1) == "'") &&
(WhereValue[i].Substring(WhereValue[i].Length - 1,1) == "'"))
{
ExcludeQuotes =
WhereValue[i].Substring(1, WhereValue[i].Length - 2);
}
}
Query.Append(ExcludeQuotes);
Query.Append("'");
if (i != WhereKey.Length - 1)
{
Query.Append(" and ");
}
}
DBManager dbManager = new
DBManager();
dbManager.ConnectionString =
ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
try
{
dbManager.Open();
AffectedRows =
dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
return (AffectedRows > 0) ?
true : false;
}
catch (Exception ex)
{
}
finally
{
dbManager.Close();
}
return false;
}
public static bool Update(string
TableName, string[] ColumnNames, string[] ColumnValues,
string[] WhereKey,
string[] WhereValue, string[] SkipColumns)
{
int NewArrayLength =
ColumnNames.Length - SkipColumns.Length;
string[] NewColumnNamesArray = new
string[NewArrayLength];
string[] NewColumnValuesArray = new
string[NewArrayLength];
int l = 0;
for (int i = 0; i <
SkipColumns.Length; i++)
{
for (int j = 0; j <
ColumnNames.Length; j++)
{
if (SkipColumns[i] !=
ColumnNames[j])
{
try
{
NewColumnNamesArray[l]
= ColumnNames[j];
NewColumnValuesArray[l] = ColumnValues[j];
l++;
}
catch (Exception ex)
{
}
}
}
}
return Update(TableName,
NewColumnNamesArray, NewColumnValuesArray, WhereKey, WhereValue);
}
public static bool Insert(string
TableName, string[] ColumnNames, string[] ColumnValues, string[] SkipColumns)
{
int NewArrayLength =
ColumnNames.Length - SkipColumns.Length;
string[] NewColumnNamesArray = new
string[NewArrayLength];
string[] NewColumnValuesArray = new
string[NewArrayLength];
int l=0;
for (int i = 0; i <
SkipColumns.Length; i++)
{
for (int j = 0; j <
ColumnNames.Length; j++)
{
if (SkipColumns[i] !=
ColumnNames[j])
{
NewColumnNamesArray[l] = ColumnNames[j];
NewColumnValuesArray[l] =
ColumnValues[j];
l++;
}
}
}
return Insert(TableName,
NewColumnNamesArray, NewColumnValuesArray);
}
public static bool Insert(string
TableName, string[] ColoumNames, string[] ColoumValues)
{
int AffectedRows = int.MinValue;
StringBuilder Query = new
StringBuilder("insert into ");
if (ColoumNames.Length != ColoumValues.Length)
{
return false;
}
Query.Append(TableName +
"(");
for (int i = 0; i <
ColoumNames.Length; i++)
{
Query.Append(ColoumNames[i]);
if (i != ColoumNames.Length - 1)
{
Query.Append(" , ");
}
}
Query.Append(")values(");
for (int i = 0; i <
ColoumValues.Length; i++)
{
Query.Append("'");
Query.Append(ColoumValues[i]);
Query.Append("'");
if (i != ColoumNames.Length - 1)
{
Query.Append(" , ");
}
}
Query.Append(")");
DBManager dbManager = new DBManager();
dbManager.ConnectionString =
ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
try
{
dbManager.Open();
AffectedRows =
dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
return (AffectedRows > 0) ?
true : false;
}
catch (Exception ex)
{
}
finally
{
dbManager.Close();
}
return false;
}
public static DataSet Select(string TableName)
{
StringBuilder Query = new
StringBuilder("Select * from ");
Query.Append(TableName);
DBManager dbManager = new DBManager();
dbManager.ConnectionString =
ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
try
{
dbManager.Open();
return
dbManager.ExecuteDataSet(System.Data.CommandType.Text, Query.ToString());
}
catch (Exception ex)
{
throw new ArgumentException("Dynamically
Generated Query Has Some Error");
}
finally
{
dbManager.Close();
}
}
public static DataSet Select(string
TableName, string[] ColoumNames, string[] WhereKey, string[] WhereValue)
{
StringBuilder Query = new
StringBuilder("Select ");
if (WhereKey.Length !=
WhereValue.Length)
{
throw new
ArgumentException("WhereKey & WhereValue Array Size Differ");
}
for (int i = 0; i < ColoumNames.Length;
i++)
{
Query.Append(ColoumNames[i]);
if (i != ColoumNames.Length - 1)
{
Query.Append(" , ");
}
}
Query.Append(" from ");
Query.Append(TableName);
Query.Append(" where ");
for (int i = 0; i <
WhereKey.Length; i++)
{
Query.Append(WhereKey[i]);
Query.Append(" = '");
Query.Append(WhereValue[i]);
Query.Append("'");
if (i != WhereKey.Length - 1)
{
Query.Append(" and
");
}
}
DBManager dbManager = new DBManager();
dbManager.ConnectionString =
ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
try
{
dbManager.Open();
return
dbManager.ExecuteDataSet(System.Data.CommandType.Text, Query.ToString());
}
catch (Exception ex)
{
throw new ArgumentException("Dynamically
Generated Query Has Some Error");
}
finally
{
dbManager.Close();
}
}
public static DataSet Select(string
TableName, string ColoumNames, string WhereKey, string WhereValue)
{
string[] whereselect =
WhereKey.Split(',');
string[] WhereselValue =
WhereValue.Split(',');
string[] wheresel =
ColoumNames.Split(',');
return
MssqlQueryBuilder.Select(TableName, wheresel, whereselect, WhereselValue);
}
}
}
If anybody find it useful feel
free to use it,it is nice of you if you provide me with any further addition
into it.
No comments:
Post a Comment