C# SQL Stored Procedure 로 DataSet 가져오기

 

 

쿼리가 아닌 프로시저를 이용하여 데이터를 가져 올 경우 아래와 같이 dataSet으로 결과 값을 받을 수 있다.

 

 

using System.Data;

using System.Data.SqlClient;

 

public DataSet GetDataFor(int param1, int param2)

{

      SqlConnection sqlConnection;

      dataSet = new DataSet();

      SqlDataAdapter sqlDataAdapter;

 

      sqlConnection = new SqlConnection(Properties.Settings.Default.AEORIONSMSConnectionString);

      sqlConnection.Open();

 

      sqlDataAdapter = new SqlDataAdapter([Replace with your sp name], sqlConnection);

      sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

 

      sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@param1", param1);

      sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@param2", param2);

 

      sqlDataAdapter.Fill(dataSet);

      sqlDataAdapter.Dispose();

 

      return dataSet;

}

 

 

 

Using the returned DataSet:

 

void GetColumns()

{

     var dataSet = GetDataFor(1, 3);

 

     string[] columnNames1 = dataSet.Tables[0].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

     string[] columnNames2 = dataSet.Tables[1].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

     string[] columnNames3 = dataSet.Tables[2].Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

}