从"常规网络错误。请检查您的网络文档"想起推销我封装的存储过程调用

看到 蝈蝈俊.Net 的一篇blog : http://blog.joycode.com/ghj/archive/2005/12/29/69703.aspx 是对"常规网络错误。请检查您的网络文档"的异常分析,其实我也遇到过这个问题.也是在给存储过程加parameter的时候类型(或大小)不合要求.后来我改进了自己封装的存储过程调用类,关键是我从存储过程中找出他需要的参数类型,再把参数一个一个按他需要的样子塞进去.这个问题就迎刃而解,不劳操心了.我觉得我的实现还是不错的呵呵.

代码有点长,关键的两句是:
SqlCommandBuilder.DeriveParameters(sqlCmd);
和
for(int i=0;i<sqlParam.Count;i++)
{
 sqlCmd.Parameters.Add(new SqlParameter(sqlParam[i].ParameterName, sqlParam[i].SqlDbType,sqlParam[i].Size,
 sqlParam[i].Direction, sqlParam[i].IsNullable, sqlParam[i].Precision, sqlParam[i].Scale,
 sqlParam[i].SourceColumn, sqlParam[i].SourceVersion, parameterValues.GetValue(i)));
}

代码:

  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Diagnostics;
  5using System.Collections;
  6using System.Xml;
  7
  8namespace crawl
  9{
 10    /**//// <summary>
 11    /// Database 的摘要说明。
 12    /// </summary>
 13    public class Database
 14    {
 15        private static SqlConnection sqlcon;
 16        private static SqlConnection sqlcon2;
 17        private static SqlConnection sqlcon3;
 18        private static Hashtable CachedStoredProcedureParam = new Hashtable();
 19
 20        public static bool SetConnectString(string p_strConnection)
 21        {
 22            try
 23            {
 24                sqlcon = new System.Data.SqlClient.SqlConnection(p_strConnection);
 25                sqlcon2 = new System.Data.SqlClient.SqlConnection(p_strConnection);
 26                sqlcon3 = new System.Data.SqlClient.SqlConnection(p_strConnection);
 27                sqlcon.Open();
 28                return true;
 29            }
 30            catch(Exception ex)
 31            {
 32                Debug.WriteLine("SetConnectString Error:" + ex.Message);
 33                return false;
 34            }
 35            finally
 36            {
 37                if(sqlcon != null && sqlcon.State != ConnectionState.Closed)
 38                    sqlcon.Close();
 39            }
 40        }
 41
 42        public static SqlConnection OpenConnection()
 43        {
 44            try
 45            {
 46                if(sqlcon.State == ConnectionState.Closed)
 47                {
 48                    sqlcon.Open();
 49                    return sqlcon;
 50                }
 51                if(sqlcon2.State == ConnectionState.Closed)
 52                {
 53                    sqlcon2.Open();
 54                    return sqlcon2;
 55                }
 56                if(sqlcon3.State == ConnectionState.Closed)
 57                {
 58                    sqlcon3.Open();
 59                    return sqlcon3;
 60                }
 61                Debug.WriteLine("No Available Connection");
 62                return null;
 63            }
 64            catch(System.Exception ex)
 65            {
 66                Debug.WriteLine("OpenConnection Error:" + ex.Message);
 67                return null;
 68            }            
 69        }
 70
 71        public static void CloseConnection(SqlConnection sqlCon)
 72        {
 73            if(sqlCon != null && sqlCon.State != ConnectionState.Closed)
 74                sqlCon.Close();
 75        }
 76        
 77        public static object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
 78        {
 79            if (storedProcedureName == null || storedProcedureName.Length == 0)
 80            {
 81                throw new ArgumentNullException("storedProcedureName");
 82            }
 83
 84            SqlParameterCollection sqlParam = FetchStoredProcedureParameters(storedProcedureName);
 85            if(parameterValues == null)
 86            {
 87                if(sqlParam.Count != 0)
 88                    throw new System.SystemException("ExecuteDataTable Error: Passing parameters do not match the required in stored procedure");
 89            }            
 90            else if(sqlParam.Count != parameterValues.Length)
 91                throw new System.SystemException("ExecuteDataTable Error: Passing parameters do not match the required in stored procedure");
 92
 93            SqlConnection sqlCon = null;
 94            try
 95            {
 96                sqlCon    = OpenConnection();
 97                SqlCommand sqlCmd        = new SqlCommand(storedProcedureName,sqlCon);
 98                sqlCmd.CommandType        = CommandType.StoredProcedure;
 99
100                for(int i=0;i<sqlParam.Count;i++)
101                {
102                    sqlCmd.Parameters.Add(new SqlParameter(sqlParam[i].ParameterName, sqlParam[i].SqlDbType,sqlParam[i].Size,
103                        sqlParam[i].Direction, sqlParam[i].IsNullable, sqlParam[i].Precision, sqlParam[i].Scale, 
104                        sqlParam[i].SourceColumn, sqlParam[i].SourceVersion, parameterValues.GetValue(i)));
105                }
106                        
107                return sqlCmd.ExecuteScalar();
108            }
109            catch(System.Exception ex)
110            {
111                Debug.WriteLine("ExecuteDataTable (" + storedProcedureName + ") Error: " + ex.Message);
112                throw;
113            }
114            finally
115            {
116                CloseConnection(sqlCon);
117            }
118        }
119        /**//// <summary>
120        /// <para>Execute the <paramref name="storedProcedureName"/> with <paramref name="parameterValues" /> and return the results in a new <see cref="DataSet"/>.</para>
121        /// </summary>
122        /// <param name="dt">
123        /// <para>A <see cref="DataTable"/> with the results of the <paramref name="storedProcedureName"/>.</para>
124        /// </param>
125        /// <param name="storedProcedureName">
126        /// <para>The stored procedure to execute.</para>
127        /// </param>
128        /// <param name="parameterValues">
129        /// <para>An array of paramters to pass to the stored procedure. The parameter values must be in call order as they appear in the stored procedure.</para>
130        /// </param>
131        /// <returns>void</returns>
132        public static void ExecuteDataTable(DataTable dt, string storedProcedureName, params object[] parameterValues)
133        {
134            if (storedProcedureName == null || storedProcedureName.Length == 0)
135            {
136                throw new ArgumentNullException("storedProcedureName");
137            }
138
139            SqlParameterCollection sqlParam = FetchStoredProcedureParameters(storedProcedureName);
140            if(parameterValues == null)
141            {
142                if(sqlParam.Count != 0)
143                    throw new System.SystemException("ExecuteDataTable Error: Passing parameters do not match the required in stored procedure");
144            }            
145            else if(sqlParam.Count != parameterValues.Length)
146                throw new System.SystemException("ExecuteDataTable Error: Passing parameters do not match the required in stored procedure");
147
148            SqlConnection sqlCon = null;
149            try
150            {
151                sqlCon    = OpenConnection();
152                SqlCommand sqlCmd        = new SqlCommand(storedProcedureName,sqlCon);
153                sqlCmd.CommandType        = CommandType.StoredProcedure;
154
155                for(int i=0;i<sqlParam.Count;i++)
156                {
157                    sqlCmd.Parameters.Add(new SqlParameter(sqlParam[i].ParameterName, sqlParam[i].SqlDbType,sqlParam[i].Size,
158                        sqlParam[i].Direction, sqlParam[i].IsNullable, sqlParam[i].Precision, sqlParam[i].Scale, 
159                        sqlParam[i].SourceColumn, sqlParam[i].SourceVersion, parameterValues.GetValue(i)));
160                }
161                        
162                SqlDataAdapter adapter    = new SqlDataAdapter(sqlCmd);
163                adapter.Fill(dt);
164            }
165            catch(System.Exception ex)
166            {
167                Debug.WriteLine("ExecuteDataTable (" + storedProcedureName + ") Error: " + ex.Message);
168                throw;
169            }
170            finally
171            {
172                CloseConnection(sqlCon);
173            }
174        }
175        /**//// <summary>
176        /// <para>Executes the <paramref name="storedProcedureName"/> using the given <paramref name="parameterValues" /> and returns the number of rows affected.</para>
177        /// </summary>
178        /// <param name="storedProcedureName">
179        /// <para>The command that contains the query to execute.</para>
180        /// </param>
181        /// <param name="parameterValues">
182        /// <para>An array of paramters to pass to the stored procedure. The parameter values must be in call order as they appear in the stored procedure.</para>
183        /// </param>
184        /// <returns>
185        /// <para>The number of rows affected</para>
186        /// </returns>
187        public static int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
188        {
189            if (storedProcedureName == null || storedProcedureName.Length == 0)
190            {
191                throw new ArgumentNullException("storedProcedureName");
192            }
193
194            SqlParameterCollection sqlParam = FetchStoredProcedureParameters(storedProcedureName);
195            if((sqlParam.Count != 0 && parameterValues == null))
196                throw new System.SystemException("ExecuteNonQuery Error: Passing parameters do not match the required in stored procedure");
197            if(parameterValues != null && sqlParam.Count != parameterValues.Length)
198                throw new System.SystemException("ExecuteNonQuery Error: Passing parameters do not match the required in stored procedure");
199            SqlConnection sqlCon = null;
200            try
201            {
202                sqlCon    = OpenConnection();
203                SqlCommand sqlCmd        = new SqlCommand(storedProcedureName,sqlCon);
204                sqlCmd.CommandType        = CommandType.StoredProcedure;
205
206                for(int i=0;i<sqlParam.Count;i++)
207                {
208                    sqlCmd.Parameters.Add(new SqlParameter(sqlParam[i].ParameterName, sqlParam[i].SqlDbType,sqlParam[i].Size,
209                        sqlParam[i].Direction, sqlParam[i].IsNullable, sqlParam[i].Precision, sqlParam[i].Scale, 
210                        sqlParam[i].SourceColumn, sqlParam[i].SourceVersion, parameterValues.GetValue(i)));
211                }
212            
213                return sqlCmd.ExecuteNonQuery();
214            }
215            catch(System.Exception ex)
216            {
217                Debug.WriteLine("ExecuteNonQuery (" + storedProcedureName + ") Error: " + ex.Message);
218                throw;
219            }
220            finally
221            {
222                CloseConnection(sqlCon);
223            }
224        }
225        /**//// <summary>
226        /// <para>Executes the <paramref name="storedProcedureName"/> with the given <paramref name="parameterValues" /> and returns an <see cref="IDataReader"></see> through which the result can be read.
227        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
228        /// </summary>
229        /// <param name="sqlCon">
230        /// <para>The opened SqlConnection</para>
231        /// </param>
232        /// <param name="storedProcedureName">
233        /// <para>The command that contains the query to execute.</para>
234        /// </param>
235        /// <param name="parameterValues">
236        /// <para>An array of paramters to pass to the stored procedure. The parameter values must be in call order as they appear in the stored procedure.</para>
237        /// </param>
238        /// <returns>
239        /// <para>An <see cref="SqlDataReader"/> SqlDataReader.</para>
240        /// </returns>
241        public static SqlDataReader ExecuteReader(SqlConnection sqlCon, string storedProcedureName, params object[] parameterValues)
242        {
243            if (sqlCon == null || sqlCon.State == ConnectionState.Closed)
244            {
245                throw new ArgumentNullException("sqlCon");
246            }
247            if (storedProcedureName == null || storedProcedureName.Length == 0)
248            {
249                throw new ArgumentNullException("storedProcedureName");
250            }
251
252            SqlParameterCollection sqlParam = FetchStoredProcedureParameters(storedProcedureName);
253            if((sqlParam.Count != 0 && parameterValues == null) || sqlParam.Count != parameterValues.Length)
254                throw new System.SystemException("ExecuteDataTable Error: Passing parameters do not match the required in stored procedure");
255
256            SqlCommand sqlCmd        = new SqlCommand(storedProcedureName,sqlCon);
257            sqlCmd.CommandType        = CommandType.StoredProcedure;
258            
259            for(int i=0;i<sqlParam.Count;i++)
260            {                
261                sqlCmd.Parameters.Add(new SqlParameter(sqlParam[i].ParameterName, sqlParam[i].SqlDbType,sqlParam[i].Size,
262                    sqlParam[i].Direction, sqlParam[i].IsNullable, sqlParam[i].Precision, sqlParam[i].Scale, 
263                    sqlParam[i].SourceColumn, sqlParam[i].SourceVersion, parameterValues.GetValue(i)));
264            }
265            
266            try
267            {
268                return sqlCmd.ExecuteReader();
269            }
270            catch(System.Exception ex)
271            {
272                Debug.WriteLine("ExecuteReader(" + storedProcedureName + ") Error: " + ex.Message);
273                throw;
274            }
275        }
276        /**//// <summary>
277        /// Fetch the parameters of the given Stored Procedure
278        /// </summary>
279        /// <param name="storedProcedureName"></param>
280        /// <returns></returns>
281        public static SqlParameterCollection FetchStoredProcedureParameters(string storedProcedureName)
282        {
283            SqlParameterCollection ret = (SqlParameterCollection)CachedStoredProcedureParam[storedProcedureName];
284            if(ret == null)
285            {
286                SqlConnection sqlCon = null;
287                try
288                {
289                    sqlCon                    = OpenConnection();
290                    SqlCommand sqlCmd        = new SqlCommand(storedProcedureName,sqlCon);
291                    sqlCmd.CommandType        = CommandType.StoredProcedure;
292                    SqlCommandBuilder.DeriveParameters(sqlCmd);
293                    sqlCmd.Parameters.RemoveAt(0);//remove the return value
294                    ret                        = sqlCmd.Parameters;
295                    CachedStoredProcedureParam[storedProcedureName] = ret;
296                }
297                catch(System.Exception ex)
298                {
299                    Debug.WriteLine("FetchStoredProcedureParameters(" + storedProcedureName + ") Error: " + ex.Message);
300                    throw;
301                }
302                finally
303                {
304                    CloseConnection(sqlCon);
305                }
306            }
307            return ret;
308        }
309    }
310}
311