How to run a database stored procedure from .NET code

Question

How to run a stored procedure from .NET code ?

matt cupryk · Answer

Private Function GetFinancialMonitoringReport() As DataSet Dim sqlAd As SqlDataAdapter Dim sqlComm As SqlCommand Dim sqlParam As SqlParameter Dim dstResults As DataSet = New DataSet() 'Dim percentVar As Integer sqlAd = New SqlDataAdapter() sqlComm = New SqlCommand() sqlComm.Connection = conSQL sqlComm.CommandType = CommandType.StoredProcedure sqlComm.CommandText = dbo.GetFinancialMonitoringReport_sp sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.DateTime dt_date_from dtpDateFrom.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.DateTime dt_date_to dtpDateTo.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_red_flag cbRedFlag.Checked) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_project_name cboProjectName.Text.Replace(' *)) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_new_provider cbNewProvider.Checked) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_provider_name cboProviderName.Text.Replace(' *)) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_funding_stream cboStream.Text.Replace(' *)) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.Decimal ii_totalcommitamt_from nudFrom.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.Decimal ii_totalcommitamt_to nudTo.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.Decimal ii_percent_variance_from nudPVarianceFrom.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.Decimal ii_percent_variance_to nudPVarianceTo.Value) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_status cboStatus.Text) sqlComm.Parameters.Add(sqlParam) sqlParam = sqlComm.CreateParameter() SetParameter(sqlParam SqlDbType.VarChar ls_advance_type_desc cboAdvanceTypeDesc.Text) sqlComm.Parameters.Add(sqlParam) sqlAd.SelectCommand = sqlComm sqlAd.Fill(dstResults) Return dstResults End Function

Krishna Garad · Answer

try this SqlCommand cmd=new SqlCommand(Sp_nameconnection); // Attach parameter to cmd cmd.CommandType=CommandType.StoredProcedure; cmd.ExecuteNonQuery();