sqlserver 取数据常用

sqlDataReader:

public SqlDataReader GetAuth_CourtListByAuth(int autIntNo)
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(mConstr);
            SqlCommand myCommand = new SqlCommand("Auth_CourtListByAuth", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
            parameterAutIntNo.Value = autIntNo;
            myCommand.Parameters.Add(parameterAutIntNo);

            // Execute the command
            myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the data reader 
            return result;
        }

List<Court> courts = new List<Court>();
CourtDB db = new CourtDB(connectionString);
SqlDataReader reader = db.GetAuth_CourtListByAuth(model.SearchAuthority);
while (reader.Read())
{
Court court = new Court();
court.CrtIntNo = Convert.ToInt32(reader["CrtIntNo"].ToString());
court.CrtName = reader["CrtDetails"].ToString();
courts.Add(court);
}
reader.Close();
courts.Insert(0, new Court() { CrtIntNo = 0, CrtName = this.Resource("msgSelectCourt") });
model.CourtList = new SelectList(courts as IEnumerable, "CrtIntNo", "CrtName");

public int GetCourtIntNoByCourtNo(string courtNo)
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(mConstr);
            SqlCommand myCommand = new SqlCommand("CourtIntNoByCourtNo", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterCourtNo = new SqlParameter("@CrtNo", SqlDbType.VarChar, 6);
            parameterCourtNo.Value = courtNo;
            myCommand.Parameters.Add(parameterCourtNo);

            SqlParameter parameterCrtIntNo = new SqlParameter("@CrtIntNo", SqlDbType.Int, 4);
            parameterCrtIntNo.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCrtIntNo);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Dispose();

                // Calculate the CustomerID using Output Param from SPROC
                int getCrtIntNo = Convert.ToInt32(parameterCrtIntNo.Value);

                return getCrtIntNo;
            }
            catch (Exception e)
            {
                myConnection.Dispose();
                string msg = e.Message;
                return 0;
            }
        }

public DataSet GetSummonsForJudgement(int crIntNo, DateTime dt, string caseNo,
            int pageSize, int pageIndex, out int totalCount, bool isPaidAtCourt = false)
        {
            SqlConnection con = new SqlConnection(this.connectionString);
            SqlCommand com = new SqlCommand("CourtRoomSummonsListForJudgement", con);
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add("@CRIntNo", SqlDbType.Int, 4).Value = crIntNo;
            com.Parameters.Add("@Date", SqlDbType.DateTime, 8).Value = dt;
            com.Parameters.Add("@CaseNo", SqlDbType.NVarChar, 50).Value = caseNo;
            com.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            com.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            com.Parameters.Add("@IsPaidAtCourt", SqlDbType.Bit).Value = isPaidAtCourt;

            SqlParameter paraTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
            paraTotalCount.Direction = ParameterDirection.Output;
            com.Parameters.Add(paraTotalCount);


            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(ds);
            da.Dispose();

            totalCount = (int)(paraTotalCount.Value == DBNull.Value ? 0 : paraTotalCount.Value);

            return ds;
        }