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_name",connection); // Attach parameter to cmd cmd.CommandType=CommandType.StoredProcedure; cmd.ExecuteNonQuery();