using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
namespace ShareLibrary.DAL
{
public class DBConnector
{
private string connectionString = null;
private SqlConnection sqlConn = null;
public DBConnector()
{
connectionString = ConfigurationManager.ConnectionStrings["ShareDBConnectionString"].ToString();
sqlConn = new SqlConnection(connectionString);
}
///
/// Return sqlConnection
///
public SqlConnection GetConnection
{
get
{
return sqlConn;
}
}
}
}
Insert Class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ShareLibrary.DAL.Gateways
{
public class DataInsertion
{
#region Member variables
private DBConnector dbConnectorObject;
private SqlConnection sqlConnectionObject;
#endregion
#region Execute query and insert data
///
/// Insert data to database
///
///
Query striing
///
public bool InsertData(string sqlString)
{
dbConnectorObject = new DBConnector();
sqlConnectionObject = new SqlConnection();
sqlConnectionObject = dbConnectorObject.GetConnection;
try
{
sqlConnectionObject.Open();
SqlCommand sqlCommandObject = new SqlCommand(sqlString, sqlConnectionObject);
int i = sqlCommandObject.ExecuteNonQuery();
if (i > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw new Exception(" Database problem.\n" + ex.Message);
}
finally
{
if (sqlConnectionObject.State == ConnectionState.Open)
{
sqlConnectionObject.Close();
}
}
}
#endregion
#region insert method with parameter
///
/// this method insert data into database
///
/// Query string
/// Sql parameter
///
public bool InsertData(string sqlString, List
{
dbConnectorObject = new DBConnector();
sqlConnectionObject = new SqlConnection();
sqlConnectionObject = dbConnectorObject.GetConnection;
try
{
sqlConnectionObject.Open();
SqlCommand sqlCommandObject = new SqlCommand(sqlString, sqlConnectionObject);
foreach (SqlParameter pr in parametersList)
{
sqlCommandObject.Parameters.Add(pr);
}
int i = sqlCommandObject.ExecuteNonQuery();
if (i > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw new Exception(" Database problem.\n" + ex.Message);
}
finally
{
if (sqlConnectionObject.State == System.Data.ConnectionState.Open)
{
sqlConnectionObject.Close();
}
}
}
#endregion
}
}
Selection:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ShareLibrary.DAL.Gateways
{
public class DataSelection
{
#region Member variables
private DBConnector dbConnectorObject;
private DataTable dataTableObject;
private SqlConnection sqlConnectionObject;
private SqlDataAdapter sqlDataAdepterObject;
private DataSet dataSetObject;
#endregion
///
/// Return selected data
///
///
selection sql query
///
public DataTable GetQueryResult(string selectingString)
{
dbConnectorObject = new DBConnector();
dataTableObject = new DataTable();
sqlConnectionObject = new SqlConnection();
dataSetObject = new DataSet();
try
{
sqlConnectionObject = dbConnectorObject.GetConnection;
sqlDataAdepterObject = new SqlDataAdapter(selectingString, sqlConnectionObject);
sqlDataAdepterObject.Fill(dataSetObject);
dataTableObject = dataSetObject.Tables[0];
if (dataSetObject.Tables[0].Rows.Count == 0)
{
return null;
}
else
{
return dataTableObject;
}
}
catch (Exception ex)
{
throw new Exception("DataBase Problem." + ex.Message);
}
finally
{
if (sqlConnectionObject.State == ConnectionState.Open)
{
sqlConnectionObject.Close();
}
}
}
}
}
///
/// serialize the object to xml string and return it
///
///
///
the object to serialize
///
public static String ObjectToXml
{
using (StringWriter s = new StringWriter())
{
XmlSerializer xs = new XmlSerializer(typeof(T));
xs.Serialize(s, xmlObject);
return s.ToString();
}
}
///
/// method to deserialize the xml data to the object type
///
///
///
///
public static T XmlToObject
{
string fixedString = xml;
if (typeof(T) == typeof(DataTable))
{
string rp = @"(?
