Пример #1
0
       /// <summary>
       /// 执行一个带参数的sql语句,并返回受此SQL语句影响的行数
       /// </summary>
       /// <param name="dParams">SQL参数</param>
       /// <returns>受此SQL语句影响的行数</returns>
       public int ExeSQL(SqlDataParamters dParams)
       {
           int iRet = 0;
           SqlConnection myCn = new SqlConnection(strConn);
           SqlCommand myCmd = new SqlCommand();
           myCmd.Connection = myCn;
           try
           {
               myCn.Open();
               myCmd.CommandType = dParams.Commandtype;
               myCmd.CommandText = dParams.CommandText;
               myCmd.Parameters.Clear();

               for (int i = 0; i < dParams.Count; i++)
               {
                   myCmd.Parameters.Add(dParams[i]);
               }
               iRet = myCmd.ExecuteNonQuery();
               myCmd.Parameters.Clear();
           }
           catch (System.Data.SqlClient.SqlException e)
           {
               iRet = -1;
               throw new Exception(e.Message);
           }
           finally
           {
               myCmd.Dispose();
               myCn.Close();
           }

           return iRet;
       }
Пример #2
0
       /// <summary>
       /// 以事务方式执行多个sql语句
       ///	</summary>
       ///	<param name="strSqls">SQL语句字符串数组</param>
       ///	<returns>True:事务成功,False:事务失败</returns>
       public bool ExeSQL(SqlDataParamters[] dParams)
       {
           bool bRet;

           SqlConnection myConn = new SqlConnection(strConn);
           myConn.Open();
           SqlTransaction trans = myConn.BeginTransaction();
           SqlCommand myCmd = new SqlCommand();
           myCmd.Transaction = trans;
           myCmd.Connection = myConn;

           try
           {
               foreach (SqlDataParamters sqlParams in dParams)
               {
                   myCmd.CommandType = sqlParams.Commandtype;
                   myCmd.CommandText = sqlParams.CommandText;
                   myCmd.Parameters.Clear();

                   for (int i = 0; i < sqlParams.Count; i++)
                   {
                       myCmd.Parameters.Add(sqlParams[i]);
                   }

                   myCmd.ExecuteNonQuery();
               }

               trans.Commit();
               bRet = true;
           }
           catch (System.Data.SqlClient.SqlException e)
           {
               trans.Rollback();
               bRet = false;
               throw new Exception(e.Message);
           }
           finally
           {
               myCmd.Dispose();
               myConn.Close();
           }

           return bRet;
       }
Пример #3
0
       /// <summary>
       /// 执行一个带参数的sql语句或存储过程,并将查询出的结果通过DataSet返回
       /// </summary>
       /// <param name="dParams">带参数的SQL</param>
       /// <param name="strTableName">查询结果返回到DataSet时填充的表名</param>
       /// <returns>查询出的结果通过DataSet返回</returns>
       public DataSet SelectDataSet(SqlDataParamters dParams, string strTableName)
       {
           DataSet ds = new DataSet();

           SqlConnection myCn = new SqlConnection(strConn);
           SqlCommand myCmd = new SqlCommand();
           myCmd.Connection = myCn;
           try
           {
               myCn.Open();
               myCmd.CommandType = dParams.Commandtype;
               myCmd.CommandText = dParams.CommandText;
               myCmd.Parameters.Clear();

               for (int i = 0; i < dParams.Count; i++)
               {
                   myCmd.Parameters.Add(dParams[i]);
               }

               SqlDataAdapter da = new SqlDataAdapter(myCmd);
               da.Fill(ds, strTableName);
               da.Dispose();
               myCmd.Dispose();
           }
           catch (System.Data.SqlClient.SqlException e)
           {
               throw new Exception(e.Message);
           }
           finally
           {
               myCmd.Dispose();
               myCn.Close();
           }

           return ds;
       }