Monday, January 21, 2013

Get Data using ADO NET DataSet with Stored Procedure

Here is the code:

        DataSet GetDataFromDatabase(string storedProcName, string param1)
            DataSet ds = new DataSet();
            //Reading database connection string from Config File
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["HKSConnectionString"].ConnectionString;
            // Establishing database connection
            using (SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString))
                // Setting SqlCommand with SQLQuery (in this case it's a stored proc name) and SqlConnection
                using (SqlCommand cmd = new System.Data.SqlClient.SqlCommand(storedProcName, sqlConnection))
                    // Letting the SqlCommand know that CommandType is 'StoredProcedure'
                    cmd.CommandType = CommandType.StoredProcedure;
                    // Adding parameters
                    cmd.Parameters.AddWithValue("@Name", param1);;
                    // DataAdapter is bind to Command
                    using (SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(cmd))
                        // Filling the dataset with result data
            return ds;

No comments:

Post a Comment