netTierGenerator

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;

using Sample.Common.Data;
using Sample.Common.Exception;
using Sample.Model.Sys;
using Sample.MsSqlDal.Util;
using Sample.IDal;
using Sample.IDal.Sys;
using Sample.Common.Util;
using Sample.Common.Sys;

namespace Sample.MsSqlDal.Sys
{
	public partial class SettingsServiceDal : ISettingsServiceDal
	{

		public SettingsServiceDal()
		{
		}

		#region Sql queries constants

		private const string GET_SETTINGS_INFO_BY_ID =
			@"SELECT rowguid, Code, Alias, CSharpType, ValueString, ValueBinary FROM tblSys_Settings WHERE rowguid = @Rowguid";

		private const string INSERT_SETTINGS_INFO =
			@"DECLARE @rowguid UNIQUEIDENTIFIER; SET @rowguid = NEWID(); INSERT INTO tblSys_Settings(rowguid, Code, Alias, CSharpType, ValueString, ValueBinary) VALUES (@rowguid, @Code, @Alias, @CSharpType, @ValueString, @ValueBinary); SELECT @rowguid, @@ERROR;";

		private const string UPDATE_SETTINGS_INFO =
			@"UPDATE tblSys_Settings SET rowguid=@Rowguid, Code=@Code, Alias=@Alias, CSharpType=@CSharpType, ValueString=@ValueString, ValueBinary=ISNULL(@ValueBinary,ValueBinary) WHERE rowguid = @Rowguid; SELECT @@ERROR";

		private const string DELETE_SETTINGS_INFO_BY_ID =
			@"DELETE FROM tblSys_Settings WHERE rowguid = @Rowguid; SELECT @@ERROR;";

		private const string GET_SETTING_BY_CODE =
			@"SELECT * FROM tblSys_Settings WHERE Code=@Code";

		#endregion

		public SettingsInfoModel GetSettingsInfoById(ISession session, Guid id)
		{
			SqlParameter prm = SqlHelperParameterCacheMy.GetSpParameter(GET_SETTINGS_INFO_BY_ID);
			if (prm == null)
			{
				prm = new SqlParameter("Rowguid", SqlDbType.UniqueIdentifier, 16);
				SqlHelperParameterCacheMy.CacheParameter(GET_SETTINGS_INFO_BY_ID, prm);
			}

			prm.Value = id;

			Session sqlSession = Session.GetSqlSession(session);
			SqlDataReader reader = sqlSession.ExecuteReader(CommandType.Text, GET_SETTINGS_INFO_BY_ID, prm);

			if (reader.HasRows)
			{
				return GetSettingsInfoModelFromDataReader(reader, true);
			}
			else
			{
				reader.Close();
				return null;
			}
		}

		public void DeleteSettingsInfoById(ISession session, Guid id)
		{
			SqlParameter prm = SqlHelperParameterCacheMy.GetSpParameter(DELETE_SETTINGS_INFO_BY_ID);
			if (prm == null)
			{
				prm = new SqlParameter("Rowguid", SqlDbType.UniqueIdentifier, 16);
				SqlHelperParameterCacheMy.CacheParameter(DELETE_SETTINGS_INFO_BY_ID, prm);
			}

			prm.Value = id;

			Session sqlSession = Session.GetSqlSession(session);
			SqlDataReader reader = sqlSession.ExecuteReader(CommandType.Text, DELETE_SETTINGS_INFO_BY_ID, prm);

			if (reader.Read())
			{
				int error = DataConvertHelper.GetInt32Value(reader[0]);
				reader.Close();

				if (error != 0)
				{
				  throw new SqlDalException(String.Concat("MS SQL Server error code = ", error.ToString()));
				}
			}
			else
			{
				reader.Close();
			}
		}

		public void InsertSettingsInfo(ISession session, SettingsInfoModel _settingsInfo)
		{
			SqlParameter[] prms = SqlHelperParameterCacheMy.GetSpParameterSet(INSERT_SETTINGS_INFO);
			if (prms == null)
			{
				prms = new SqlParameter[5];
				prms[0] = new SqlParameter("Code", SqlDbType.NVarChar, 100);
				prms[1] = new SqlParameter("Alias", SqlDbType.NVarChar, 100);
				prms[2] = new SqlParameter("CSharpType", SqlDbType.TinyInt, 1);
				prms[3] = new SqlParameter("ValueString", SqlDbType.NVarChar);
				prms[4] = new SqlParameter("ValueBinary", SqlDbType.VarBinary);

				SqlHelperParameterCacheMy.CacheParameterSet(INSERT_SETTINGS_INFO, prms);
			}

			prms[0].Value = DataConvertHelper.GetDbValue(_settingsInfo.Code);
			prms[1].Value = DataConvertHelper.GetDbValue(_settingsInfo.Alias);
			prms[2].Value = DataConvertHelper.GetDbValue<CSharpTypeEnum>(_settingsInfo.CSharpType);
			prms[3].Value = DataConvertHelper.GetDbValue(_settingsInfo.ValueString);
			prms[4].Value = DataConvertHelper.GetDbValue(_settingsInfo.ValueBinary);

			Session sqlSession = Session.GetSqlSession(session);
			SqlDataReader reader = sqlSession.ExecuteReader(CommandType.Text, INSERT_SETTINGS_INFO, prms);

			if (reader.Read())
			{
				_settingsInfo.Id = DataConvertHelper.GetGuidValue(reader[0]);
				int error = DataConvertHelper.GetInt32Value(reader[1]);
				reader.Close();

				if (error != 0)
				{
					throw new SqlDalException(String.Concat("MS SQL Server error code = ", error.ToString()));
				}
			}
			else
			{
				reader.Close();
			}
		}

		public void UpdateSettingsInfo(ISession session, SettingsInfoModel _settingsInfo)
		{
			SqlParameter[] prms = SqlHelperParameterCacheMy.GetSpParameterSet(UPDATE_SETTINGS_INFO);
			if (prms == null)
			{
				prms = new SqlParameter[6];
				prms[0] = new SqlParameter("Rowguid", SqlDbType.UniqueIdentifier, 16);
				prms[1] = new SqlParameter("Code", SqlDbType.NVarChar, 100);
				prms[2] = new SqlParameter("Alias", SqlDbType.NVarChar, 100);
				prms[3] = new SqlParameter("CSharpType", SqlDbType.TinyInt, 1);
				prms[4] = new SqlParameter("ValueString", SqlDbType.NVarChar);
				prms[5] = new SqlParameter("ValueBinary", SqlDbType.VarBinary);

				SqlHelperParameterCacheMy.CacheParameterSet(UPDATE_SETTINGS_INFO, prms);
			}

			prms[0].Value = DataConvertHelper.GetDbValue(_settingsInfo.Id);
			prms[1].Value = DataConvertHelper.GetDbValue(_settingsInfo.Code);
			prms[2].Value = DataConvertHelper.GetDbValue(_settingsInfo.Alias);
			prms[3].Value = DataConvertHelper.GetDbValue<CSharpTypeEnum>(_settingsInfo.CSharpType);
			prms[4].Value = DataConvertHelper.GetDbValue(_settingsInfo.ValueString);
			prms[5].Value = DataConvertHelper.GetDbValue(_settingsInfo.ValueBinary);

			Session sqlSession = Session.GetSqlSession(session);
			SqlDataReader reader = sqlSession.ExecuteReader(CommandType.Text, UPDATE_SETTINGS_INFO, prms);

			if (reader.Read())
			{
				int error = DataConvertHelper.GetInt32Value(reader[0]);
				reader.Close();

				if (error != 0)
				{
					throw new SqlDalException(String.Concat("MS SQL Server error code = ", error.ToString()));
				}
			}
			else
			{
				reader.Close();
			}
		}

		internal static SettingsInfoModel GetSettingsInfoModelFromDataReader(SqlDataReader reader, bool needReaderClose)
		{
			SettingsInfoModel _settingsInfo = null;

			if (!reader.IsClosed)
			{
				reader.Read();

				int ordId = reader.GetOrdinal("rowguid"); Guid _id = reader.IsDBNull(ordId) ? Guid.Empty : reader.GetGuid(ordId);
				int ordCode = reader.GetOrdinal("Code"); string _code = reader.IsDBNull(ordCode) ? String.Empty : reader.GetString(ordCode);
				int ordAlias = reader.GetOrdinal("Alias"); string _alias = reader.IsDBNull(ordAlias) ? String.Empty : reader.GetString(ordAlias);
				int ordCSharpType = reader.GetOrdinal("CSharpType"); CSharpTypeEnum _cSharpType = (CSharpTypeEnum)(reader.IsDBNull(ordCSharpType) ? Byte.MinValue : reader.GetByte(ordCSharpType));
				int ordValueString = reader.GetOrdinal("ValueString"); string _valueString = null; if (!reader.IsDBNull(ordValueString)) _valueString = reader.GetString(ordValueString);
				int ordValueBinary = reader.GetOrdinal("ValueBinary"); byte[] _valueBinary = null; DataConvertHelper.GetBinaryValue(reader["ValueBinary"]);

				_settingsInfo = new SettingsInfoModel(_id, _code, _alias, _cSharpType, _valueString, _valueBinary);

				if (needReaderClose)
				{
					reader.Close();
				}
			}
			else
			{
				_settingsInfo = new SettingsInfoModel();
			}

			return _settingsInfo;
		}

		internal static IList<SettingsInfoModel> GetSettingsInfoModelListFromDataReader(SqlDataReader reader, bool needReaderClose)
		{
			IList<SettingsInfoModel> list = new List<SettingsInfoModel>();

			if (!reader.IsClosed)
			{
				while (reader.Read())
				{
					int ordId = reader.GetOrdinal("rowguid"); Guid _id = reader.IsDBNull(ordId) ? Guid.Empty : reader.GetGuid(ordId);
					int ordCode = reader.GetOrdinal("Code"); string _code = reader.IsDBNull(ordCode) ? String.Empty : reader.GetString(ordCode);
					int ordAlias = reader.GetOrdinal("Alias"); string _alias = reader.IsDBNull(ordAlias) ? String.Empty : reader.GetString(ordAlias);
					int ordCSharpType = reader.GetOrdinal("CSharpType"); CSharpTypeEnum _cSharpType = (CSharpTypeEnum)(reader.IsDBNull(ordCSharpType) ? Byte.MinValue : reader.GetByte(ordCSharpType));
					int ordValueString = reader.GetOrdinal("ValueString"); string _valueString = null; if (!reader.IsDBNull(ordValueString)) _valueString = reader.GetString(ordValueString);
					int ordValueBinary = reader.GetOrdinal("ValueBinary"); byte[] _valueBinary = null; DataConvertHelper.GetBinaryValue(reader["ValueBinary"]);

					SettingsInfoModel item = new SettingsInfoModel(_id, _code, _alias, _cSharpType, _valueString, _valueBinary);

					list.Add(item);
				}

				if (needReaderClose)
				{
					reader.Close();
				}
			}

			return list;
		}

		public SettingsInfoModel GetSettingByCode(ISession session, string code)
		{
			SqlParameter prm = SqlHelperParameterCacheMy.GetSpParameter(GET_SETTING_BY_CODE);
			if (prm == null)
			{
				prm = new SqlParameter("Code", SqlDbType.NVarChar, 100);
				SqlHelperParameterCacheMy.CacheParameter(GET_SETTING_BY_CODE, prm);
			}

			prm.Value = DataConvertHelper.GetDbValue(code);

			Session sqlSession = Session.GetSqlSession(session);
			SqlDataReader reader = sqlSession.ExecuteReader(CommandType.Text, GET_SETTING_BY_CODE, prm);

			if (reader.HasRows)
			{
				return GetSettingsInfoModelFromDataReader(reader, true);
			}
			else
			{
				reader.Close();
				return null;
			}
		}

	}
}