----------------
xml version="1.0"?>
configuration>
system.web>
compilation debug="true"/>
authentication mode="Windows"/>
system.web>
connectionStrings>
add name="myconn" connectionString="Server='10.0.0.15';Integrated Security=false;uid='sa';pwd='123456';Database=Asian_Test;Min Pool Size=10" providerName="System.Data.SqlClient"/>
connectionStrings>
appSettings>
add key="DBSERVER" value="10.0.0.15"/>
add key="DBNAME" value="Asian_Test"/>
add key="USENAME" value="sa"/>
add key="password" value="123456"/>
appSettings>
configuration>
----------------------
data access
using System;
using System.Collections.Generic;
using System.Text;
using sits.@in.db.conect;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace sits.@in.db.conect.IMPL
{
public class DataAccessImpl : sits.@in.db.conect.DataAccess
{
private string fConectingString = string.Empty;
public string ConectingString
{
get { return this.fConectingString; }
set { this.fConectingString = value; }
}
public DataAccessImpl()
{
fConectingString = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
}
//string UserID = ConfigurationManager.AppSettings["USENAME"].ToString();
//string Password = ConfigurationManager.AppSettings["password"].ToString();
//string Host = ConfigurationManager.AppSettings["DBNAME"].ToString();
//string ServiceName = ConfigurationManager.AppSettings["DBSERVER"].ToString();
#region DataAccess Members
public DataSet readtodataset(string sql)
{
DataSet ds = null;
try
{
ds = SqlHelper.ExecuteDataset(ConectingString, CommandType.Text, sql);
}
catch (Exception ex)
{
throw;
}
return ds;
}
public bool updatesql(string sql)
{
bool success = false;
try
{
SqlHelper.ExecuteNonQuery(ConectingString, CommandType.Text, sql);
success = true;
}
catch (Exception ex)
{
throw ex;
}
return success;
}
public bool updatesSP(string sSP, SqlParameter[] param)
{
bool success = false;
try
{
SqlHelper.ExecuteNonQuery(ConectingString, CommandType.StoredProcedure, sSP, param);
success = true;
}
catch (Exception ex)
{
throw ex;
}
return success;
}
#endregion
}
}
----------------------------
public ItemCategory[] getActiveCategories()
{
logger.Debug("start retriviewing all item category details");
ItemCategory[] tempCat = null;
string sSql = "SELECT * FROM IMS_TBL_ADMIN_ITEMCATEGORY WHERE CATEGORY_STATUS=" + 1 + " ORDER BY CATEGORY_NAME";
try
{
DataSet ds = dataAccess.ReadToDataSet(sSql);
int rowCount = ds.Tables[0].Rows.Count;
int row = 0;
if (rowCount > 0)
{
tempCat = new ItemCategory[rowCount];
foreach (DataRow dr in ds.Tables[0].Rows)
{
ItemCategory temp = new ItemCategory();
temp.categoryCode = dr["CATEGORY_CODE"].ToString().Trim();
temp.categoryName = dr["CATEGORY_NAME"].ToString().Trim();
temp.userDefineCode = dr["DEFINE_CAT_CODE"].ToString().Trim();
if (!string.IsNullOrEmpty(dr["CATEGORY_STATUS"].ToString()))
{
temp.categoryStatus = Convert.ToInt32(dr["CATEGORY_STATUS"]);
}
else
{
temp.categoryStatus = 0;
}
tempCat[row++] = temp;
}
}
logger.Debug("Successfully retrived all item category details");
}
catch (DbException db)
{
logger.Error("Item category information retrieval failed due to the exception : " + db.Message);
throw new DbException("DB error when retriving the item category details");
}
catch (Exception ex)
{
logger.Error("Item category information retrieval failed due to the exception : " + ex.Message);
throw ex;
}
return tempCat;
-------------------------------------
public ItemCategory getCategoryByCode(string code)
{
logger.Debug("Start retriviewing Item category details by code" + code);
ItemCategory tempCat = new ItemCategory();
string sSql = "SELECT * FROM IMS_TBL_ADMIN_ITEMCATEGORY WHERE DEFINE_CAT_CODE='" + code + "'";
try
{
DataSet ds = dataAccess.ReadToDataSet(sSql);
int aa = ds.Tables[0].Rows.Count;
DataRow dr = ds.Tables[0].Rows[0];
tempCat.categoryCode = dr["CATEGORY_CODE"].ToString().Trim();
tempCat.categoryName = dr["CATEGORY_NAME"].ToString().Trim();
tempCat.userDefineCode = dr["DEFINE_CAT_CODE"].ToString().Trim();
if (!string.IsNullOrEmpty(dr["CATEGORY_STATUS"].ToString()))
{
tempCat.categoryStatus = Convert.ToInt32(dr["CATEGORY_STATUS"]);
}
else
{
tempCat.categoryStatus = 0;
}
logger.Debug("successfully retrived Item category details by code" + code);
}
catch (DbException db)
{
logger.Error("Item category information retrieval failed due to the exception : " + db.Message);
throw new DbException("DB error when retriving the item category details");
}
catch (Exception ex)
{
logger.Error("Item category information retrieval failed due to the exception : " + ex.Message);
throw ex;
}
return tempCat;
____-------------------------------
public bool InsertAccount(Account Account)
{
logger.Debug("Inserting account information for the account : " + Account.accountName);
bool operationSuccess = false;
try
{
SqlParameter[] paramArray = new SqlParameter[14];
paramArray[0] = new SqlParameter("@AccCode",SqlDbType.Char,10);
paramArray[0].Value = Account.accountCode;
paramArray[1] = new SqlParameter("@AccName",SqlDbType.Char,50);
paramArray[1].Value = Account.accountName;
paramArray[2] = new SqlParameter("@AccAddress",SqlDbType.Char,80);
paramArray[2].Value = Account.accountAddress;
paramArray[3] = new SqlParameter("@AccTel",SqlDbType.Char,30);
paramArray[3].Value = Account.accountTel;
paramArray[4] = new SqlParameter("@AccFax",SqlDbType.Char,30);
paramArray[4].Value = Account.accountFax;
paramArray[5] = new SqlParameter("@AccEmail",SqlDbType.Char,30);
paramArray[5].Value = Account.accountEmail;
paramArray[6] = new SqlParameter("@LastLogOnUser",SqlDbType.Char,20);
paramArray[6].Value = Account.lastLogOnUser;
paramArray[7] = new SqlParameter("@AccType",SqlDbType.Int);
paramArray[7].Value = Account.accountType;
paramArray[8] = new SqlParameter("@Category",SqlDbType.Int);
paramArray[8].Value = Account.accountCategory;
//paramArray[9] = new SqlParameter("@BalanceDate",SqlDbType.DateTime);
//paramArray[9].Value = Account.balanceDate;
paramArray[9] = new SqlParameter("@MainAccID",SqlDbType.Int);
paramArray[9].Value = Account.mainAccountId;
paramArray[10] = new SqlParameter("@DisplayName", SqlDbType.Char,60);
paramArray[10].Value = Account.displayName;
paramArray[11] = new SqlParameter("@Active", SqlDbType.Bit);
paramArray[11].Value = Account.active;
paramArray[12] = new SqlParameter("@CostCentre", SqlDbType.Int);
paramArray[12].Value = Account.costCenter;
paramArray[13] = new SqlParameter("@Refno", SqlDbType.VarChar,12);
paramArray[13].Value = Account.referenceNo;
operationSuccess = fdataAccess.dbUpdateSP("Acc_sp_Insert_Account", paramArray);
logger.Debug("Account infomation for account : " + Account.accountName + " successfully saved");
}
catch (DbException db)
{
logger.Error("Inserting account : " + Account.accountName + " failed with the exception : " + db.Message);
throw new DbException("DB error when Inserting Account");
}
return operationSuccess;
}
