1using System;
  2using System.Collections.Generic;
  3using System.Data;
  4using System.Data.Common;
  5using System.Data.SqlClient;
  6using System.Reflection;
  7
  8using IFNuke;
  9
 10namespace IFNuke.Data
 11...{
 12    public class SqlDataProvider : DataProvider
 13    ...{
 14        private const string ProviderType = "data";
 15
 16        private ProviderConfiguration _providerConfiguration;
 17        private string _connectionString;
 18        //private string _providerPath;
 19        //private string _objectQualifier;
 20        //private string _databaseOwner;
 21        //private string _upgradeConnectionString;
 22
 23        public SqlDataProvider()
 24        ...{
 25            _providerConfiguration  = ProviderConfiguration.GetProviderConfiguration(ProviderType);
 26            Provider provider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];
 27            _connectionString = Config.GetConnectionString();
 28            //if (_connectionString == string.Empty)
 29            //    _connectionString = provider.Attributes["connectionString"];
 30
 31            //if (provider.Attributes["upgradeConnectionString"].ToString() != string.Empty)
 32            //    _upgradeConnectionString = provider.Attributes["upgradeConnectionString"].ToString();
 33            //else
 34            //    _upgradeConnectionString = _connectionString;
 35
 36            //_providerPath = Config.GetProviderPath(ProviderType);
 37            //_objectQualifier = Config.GetObjectQualifer();
 38            //_databaseOwner = Config.GetDataBaseOwner();
 39        }
 40
 41        public override int ExecuteNonQuery(CommandType commandType, string commandText, CriteriaCollection parameters)
 42        ...{
 43            if (_connectionString == null || _connectionString.Length == 0) throw new ArgumentNullException("connectionString is null.");
 44            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText is null.");
 45
 46            // Create & open a SqlConnection, and dispose of it after we are done
 47            using (SqlConnection connection = new SqlConnection(_connectionString))
 48            ...{
 49                SqlCommand command = new SqlCommand(commandText, connection);
 50                command.CommandType = commandType;
 51
 52                if (commandType == CommandType.StoredProcedure)
 53                ...{
 54                    if (parameters != null && parameters.Count > 0)
 55                    ...{
 56                        foreach (Criteria parameter in parameters)
 57                        ...{
 58                            if (parameter.Direction == ParameterDirection.Output)
 59                            ...{
 60                                SqlParameter outParameter = new SqlParameter(parameter.ParameterName, ConvertDbTypeToSqlDbType(parameter.DbType), parameter.Size);
 61                                outParameter.Direction = ParameterDirection.Output;
 62                                command.Parameters.Add(outParameter);
 63                            }
 64                            else
 65                                command.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
 66                        }
 67                    }
 68                }
 69
 70                connection.Open();
 71                int retValue = command.ExecuteNonQuery();
 72                // set output parameters value\
 73                if (parameters != null && parameters.Count > 0)
 74                ...{
 75                    foreach (Criteria parameter in parameters)
 76                    ...{
 77                        if (parameter.Direction == ParameterDirection.Output)
 78                        ...{
 79                            parameter.Value = command.Parameters[parameter.ParameterName].Value;
 80                        }
 81                    }
 82                }
 83                return retValue;
 84            }
 85        }
 86
 87        public override DataSet ExecuteDataSet(CommandType commandType, string commandText, CriteriaCollection parameters)
 88        ...{
 89            DataSet ds = new DataSet();
 90            using (SqlConnection connection = new SqlConnection(_connectionString))
 91            ...{
 92                SqlCommand command = new SqlCommand(commandText, connection);
 93                command.CommandType = commandType;
 94
 95                if (commandType == CommandType.StoredProcedure)
 96                ...{
 97                    if (parameters != null && parameters.Count > 0)
 98                    ...{
 99                        foreach (Criteria parameter in parameters)
100                        ...{
101                            if (parameter.Value != null && parameter.Value.ToString() != "")
102                                command.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
103                        }
104                    }
105                }
106
107                connection.Open();
108                SqlDataAdapter da = new SqlDataAdapter(command);
109                da.Fill(ds);
110            }
111            return ds;
112        }
113
114        public override DataSet ExecuteDataSet(CommandType commandType, string commandText, CriteriaCollection parameters, int pageSize, int pageNumber, out int totalRecord)
115        ...{
116            DataSet ds = new DataSet();
117            using (SqlConnection connection = new SqlConnection(_connectionString))
118            ...{
119                SqlCommand command = new SqlCommand(commandText, connection);
120                command.CommandType = commandType;
121
122                if (commandType == CommandType.StoredProcedure)
123                ...{
124                    if (parameters != null && parameters.Count > 0)
125                    ...{
126                        foreach (Criteria parameter in parameters)
127                        ...{
128                            if (parameter.Value != null && parameter.Value.ToString() != "")
129                                command.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
130                        }
131                    }
132
133                    // if (pageSize == 0) pageSize = 20;
134                    // if (pageNumber == 0) pageNumber = 1;
135                    command.Parameters.AddWithValue("@PageSize", pageSize);
136                    command.Parameters.AddWithValue("@PageNumber", pageNumber);
137
138                    SqlParameter totalRecordParameter = new SqlParameter("@TotalRecord", SqlDbType.Int, 10);
139                    totalRecordParameter.Direction = ParameterDirection.Output;
140                    command.Parameters.Add(totalRecordParameter);
141
142                    //SqlParameter totalPageParameter = new SqlParameter("@TotalPage", SqlDbType.Int, 10);
143                    //totalPageParameter.Direction = ParameterDirection.Output;
144                    //command.Parameters.Add(totalPageParameter);
145                }
146
147                connection.Open();
148                SqlDataAdapter da = new SqlDataAdapter(command);
149                da.Fill(ds);
150                totalRecord = (int)command.Parameters["@TotalRecord"].Value;
151                // totalPage = (int)command.Parameters["@TotalPage"].Value;
152            }
153            return ds;
154        }
155
156        public override object ExecuteScalar(CommandType commandType, string commandText, CriteriaCollection parameters)
157        ...{
158            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
159            if (_connectionString == null || _connectionString.Length == 0) throw new ArgumentNullException("connectionString");
160
161            using (SqlConnection connection = new SqlConnection(_connectionString))
162            ...{
163                SqlCommand command = new SqlCommand(commandText, connection);
164                command.CommandType = commandType;
165
166                if (commandType == CommandType.StoredProcedure)
167                ...{
168                    command.CommandType = CommandType.StoredProcedure;
169                    if (parameters != null && parameters.Count > 0)
170                    ...{
171                        foreach (Criteria parameter in parameters)
172                        ...{
173                            command.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
174                        }
175                    }
176                }
177
178                connection.Open();
179                return command.ExecuteScalar();
180            }
181        }
182
183        public override IDataReader ExecuteReader(CommandType commandType, string commandText, CriteriaCollection parameters)
184        ...{
185            // need later implement
186            return (IDataReader)(new Object());
187        }
188
189        public override T Select<T>(int id)
190        ...{
191            string tableName = GetTableName<T>();
192            string idName = GetIdName<T>();
193            T bo = default(T);
194            using (SqlConnection connection = new SqlConnection(_connectionString))
195            ...{
196                SqlCommand command = new SqlCommand(tableName + "_Select", connection);
197                command.CommandType = CommandType.StoredProcedure;
198                command.Parameters.AddWithValue("@" + idName, id);
199
200                connection.Open();
201                using (SqlDataReader myReader = command.ExecuteReader())
202                ...{
203                    if (myReader.Read())
204                    ...{
205                        bo = CreateBO<T>(myReader);
206                    }
207                    myReader.Close();
208                }
209                connection.Close();
210            }
211            return bo;
212        }
213
214        public override DataSet Select<T>(string spName, CriteriaCollection parameters, int pageIndex, int pageSize, out int totalRecord)
215        ...{
216            if (spName == null || spName == string.Empty || spName == "TableName_Select")
217                spName = GetTableName<T>() + "_Select";
218            DataSet ds = new DataSet();
219            using (SqlConnection connection = new SqlConnection(_connectionString))
220            ...{
221                SqlCommand command = new SqlCommand(spName, connection);
222                command.CommandType = CommandType.StoredProcedure;
223
224                if (parameters != null && parameters.Count > 0)
225                ...{
226                    foreach (Criteria parameter in parameters)
227                    ...{
228                        if (parameter.Value != null && parameter.Value.ToString() != "")
229                            command.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
230                    }
231                }
232
233                command.Parameters.AddWithValue("@PageSize", pageSize);
234                command.Parameters.AddWithValue("@PageNumber", pageIndex);
235
236                SqlParameter totalRecordParameter = new SqlParameter("@TotalRecord", SqlDbType.Int, 10);
237                totalRecordParameter.Direction = ParameterDirection.Output;
238                command.Parameters.Add(totalRecordParameter);
239
240                connection.Open();
241                SqlDataAdapter da = new SqlDataAdapter(command);
242                da.Fill(ds);
243
244                totalRecord = (int)command.Parameters["@TotalRecord"].Value;
245
246            }
247            return ds;
248        }
249
250        public override List<T> SelectList<T>(string spName, CriteriaCollection parameters, int pageIndex, int pageSize, out int totalRecord)
251        ...{
252            if (spName == null || spName == string.Empty || spName == "TableName_Select")
253                spName = GetTableName<T>() + "_Select";
254            List<T> tempList = new List<T>();
255            using (SqlConnection connection = new SqlConnection(_connectionString))
256            ...{
257                SqlCommand command = new SqlCommand(spName, connection);
258                command.CommandType = CommandType.StoredProcedure;
259
260                if (parameters != null && parameters.Count > 0)
261                ...{
262                    foreach (Criteria parameter in parameters)
263                    ...{
264                        if (parameter.Value != null && parameter.Value.ToString() != "")
265                            command.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
266                    }
267                }
268
269                command.Parameters.AddWithValue("@PageSize", pageSize);
270                command.Parameters.AddWithValue("@PageNumber", pageIndex);
271
272                SqlParameter totalRecordParameter = new SqlParameter("@TotalRecord", SqlDbType.Int, 10);
273                totalRecordParameter.Direction = ParameterDirection.Output;
274                command.Parameters.Add(totalRecordParameter);
275
276                connection.Open();
277                using (SqlDataReader myReader = command.ExecuteReader())
278                ...{
279                    if (myReader.HasRows)
280                    ...{
281                        while (myReader.Read())
282