1 namespace Nik.Data.ORM.DataSource.MSSqlCLient
  2 {
  3 	/// <summary>
  4 	/// Источник данных 
  5 	/// </summary>
  6 	public class MSSQLDataSource
  7 	{
  8 		public MSSQLDataSource(string sqlQuery)
  9 		{
 10 			connection = DataSession.Current.ConnectionPool.GetConnection();
 11 			Command = new SqlCommand(sqlQuery, connection);
 12 		}
 13 
 14 		public SqlCommand Command;
 15 		public SqlConnection connection;
 16 
 17 		/// <summary>
 18 		/// Текст запроса
 19 		/// </summary>
 20 		public string QueryText
 21 		{
 22 			get { return Command.CommandText; }
 23 			set { Command.CommandText=value; }
 24 		}
 25 
 26 		public SqlParameterCollection Parameters
 27 		{
 28 			get { return Command.Parameters; }
 29 		}
 30 
 31 		/// <summary>
 32 		/// Добавить параметр, без @
 33 		/// </summary>
 34 		/// <param name="parametrName">Имя параметра</param>
 35 		/// <param name="value">Значение параметра</param>
 36 		public IDataParameter AddParameter(string parametrName, object value)
 37 		{
 38 			IDataParameter parameter = Command.Parameters.Add("@" + parametrName, value);
 39 			if (value is int) parameter.DbType=DbType.Int32;
 40 			if (value is bool) parameter.DbType=DbType.Boolean;
 41 			return parameter;
 42 		}
 43 		/// <summary>
 44 		/// Добавить выходной параметр
 45 		/// </summary>
 46 		/// <param name="parametrName">Имя параметра</param>
 47 		/// <param name="value">Значение параметра</param>
 48 		/// <returns>Дoбавленный параметр</returns>
 49 		public IDataParameter AddOutputParameter(string parametrName, object value)
 50 		{
 51 			IDataParameter parameter = Command.Parameters.Add("@" + parametrName, value);
 52 			parameter.Direction = ParameterDirection.Output;
 53 			return parameter;
 54 		}
 55 
 56 		/// <summary>
 57 		/// Выполнить команду(без возврата результата)
 58 		/// </summary>
 59 		public void ExecuteCommand()
 60 		{
 61 			try
 62 			{
 63 				EnshureConnectuionIsOpen();
 64 				Command.ExecuteNonQuery();
 65 				Close();
 66 			}
 67 			catch (Exception ex)
 68 			{
 69 				Close();
 70 
 71 
 72 				throw new ApplicationException("Error. Method: DBDataSource.ExecuteCommand()\n SQLQuery: " + Command.CommandText + "\n" + Paramss + "\n Error message: " + ex.Message);
 73 			}
 74 		}
 75 
 76 		private string Paramss
 77 		{
 78 			get
 79 			{
 80 				string paramss = "\nParams\n------------\n";
 81 				foreach (SqlParameter p  in Command.Parameters)
 82 				{
 83 					paramss+= p.ParameterName + " = " + (p.Value!=null ? p.Value.ToString() : "NULL") + "\n";
 84 				}
 85 				paramss+="===============================";
 86 				return paramss;
 87 			}
 88 		}
 89 
 90 		/// <summary>
 91 		/// Выполнить и получить скаляр
 92 		/// </summary>
 93 		/// <returns></returns>
 94 		public object ExecuteScalar()
 95 		{
 96 			try
 97 			{
 98 				EnshureConnectuionIsOpen();
 99 				object o = Command.ExecuteScalar();
100 				Close();
101 				return o;
102 			}
103 			catch (Exception ex)
104 			{
105 				Close();
106 				throw new Exception("Error. Method: DBDataSource.ExecuteScalar()\n\n SQLQuery: " + Command.CommandText + "\n" + Paramss + "\n Error message: " + ex.Message);
107 			}
108 
109 		}
110 
111 		public DataTable ExecuteDatatable()
112 		{
113 			DataTable result = new DataTable();
114 			DataTable shema = new DataTable();
115 			SqlDataReader sqlDataReader = null;
116 
117 			object[] row;
118 
119 			try
120 			{
121 				EnshureConnectuionIsOpen();
122 				sqlDataReader = Command.ExecuteReader();
123 				shema = sqlDataReader.GetSchemaTable();
124 				row = new object[sqlDataReader.FieldCount];
125 
126 				for (int i = 0; i < sqlDataReader.FieldCount; i++)
127 				{
128 					result.Columns.Add(shema.Rows[i]["ColumnName"].ToString(),
129 						((Type) shema.Rows[i]["DataType"]));
130 				}
131 
132 				while (sqlDataReader.Read())
133 				{
134 					sqlDataReader.GetValues(row);
135 					result.Rows.Add(row);
136 				}
137 			}
138 			catch (Exception ex)
139 			{
140 				throw(ex);
141 			}
142 			finally
143 			{
144 				if (sqlDataReader != null)
145 					sqlDataReader.Close();
146 
147 				if (Command.Connection.State != ConnectionState.Closed)
148 					Command.Connection.Close();
149 			}
150 			return result;
151 		}
152 
153 		/// <summary>
154 		/// Выполнить и получить DataReader
155 		/// </summary>
156 		/// <returns>Результат выполнения команды</returns>
157 		public IDataReader Execute()
158 		{
159 			try
160 			{
161 				EnshureConnectuionIsOpen();
162 				return Command.ExecuteReader();
163 
164 			}
165 			catch (Exception ex)
166 			{	
167 				Close();
168 				throw new Exception("Error. Method: DBDataSource.ExecuteReader()\n\n SQLQuery: " + Command.CommandText + "\n" + Paramss + "\n Error message: " + ex.Message);
169 			}
170 		}
171 
172 		/// <summary>
173 		/// Закрыть источник 
174 		/// </summary>
175 		public void Close()
176 		{
177 			DataSession.Current.ConnectionPool.ReturnBackConnection(connection);
178 		}
179 
180 		/// <summary>
181 		/// Убедиться что соединение открыто
182 		/// </summary>
183 		private void EnshureConnectuionIsOpen()
184 		{
185 			if (connection.State != ConnectionState.Open)
186 			{
187 				connection.Open();
188 			}
189 		}
190 
191 		#region IDisposable Members
192 
193 		public void Dispose()
194 		{
195 			Close();
196 		}
197 
198 		#endregion
199 	}
200 
201 	/// <summary>
202 	/// Источник данных  - SQL запрос
203 	/// </summary>
204 	public class Request : MSSQLDataSource
205 	{
206 		/// <summary>
207 		/// Конструктор SQL запроса
208 		/// </summary>
209 		/// <param name="sqlString">SQL запрос</param>
210 		public Request(string sqlString) : base(sqlString)
211 		{
212 			Command.CommandType = CommandType.Text;
213 		}
214 	}
215 
216 	/// <summary>
217 	/// Источник данных - хранимая процедура
218 	/// </summary>
219 	public class StoredProcedure : MSSQLDataSource
220 	{
221 		/// <summary>
222 		/// Конструктор ХП
223 		/// </summary>
224 		/// <param name="storedProcName">Имя хранимой процедуры</param>
225 		public StoredProcedure(string storedProcName) : base(storedProcName)
226 		{
227 			Command.CommandType = CommandType.StoredProcedure;
228 		}
229 
230 	}
231 }
232