Use Stored Procedure in C# Web Part

[Simon]

I have a problem using a stored procedure in C#. Im using SQL2000 and
C# with web parts.

Currently i put the entire sql query in the CommandText. Then it works
correctly and gives the companynames in my column. As u can see below
in the C# code.

I made a Stored Procedure from the query and want to call that instead,
it saves a lot of code in my project.

What lines do i have to change, and how, to make this Stored Procedure
work and give the same output as my sql string in the code does now.

=======C# code with long SQL query in it========

protected override void CreateChildControls()
{

tbSearchC = new TextBox();
btnSearchC = new Button();
btnSearchC.Text = "Find";
btnSearchC.Click += new EventHandler(this.btnSearchC_SearchAction );
Controls.Add(tbSearchC);
Controls.Add(btnSearchC);
dgDataC_search = new DataGrid();
dgDataC_search.HeaderStyle.BorderWidth = 2;
dgDataC_search.HeaderStyle.Font.Bold = true;
dgDataC_search.HeaderStyle.BackColor = Color.LightSteelBlue;
dgDataC_search.HeaderStyle.ForeColor = Color.Black;
dgDataC_search.HeaderStyle.Wrap = false;
dgDataC_search.GridLines = System.Web.UI.WebControls.GridLines.Both;
dgDataC_search.SelectedItemStyle.BackColor =
System.Drawing.Color.YellowGreen;
dgDataC_search.AutoGenerateColumns = false;
BoundColumn newColumn_Companyname;
newColumn_Companyname = new BoundColumn();
newColumn_Companyname.DataField = "name";
newColumn_Companyname.HeaderText = "Companyname:";
newColumn_Companyname.HeaderStyle.Width=120;
dgDataC_search.Columns.Add(newColumn_Companyname);

Controls.Add(dgDataC_search);
}

private void btnSearchC_SearchAction(object sender, System.EventArgs e)
{

string sqlString ="Persist Security Info=False;Integrated
Security=SSPI;initial catalog=SIX;server=(local)";
SqlConnection sqlCon = new SqlConnection(sqlString);
sqlCon.Open();
SqlCommand sc_findC = new SqlCommand();
sc_findC.Connection = sqlCon;
sc_findC.CommandType = CommandType.Text;

sc_findC.CommandText ="select contact.contact_id, contact.[name],
isnull(address.city, '') as city, isnull(person.mrmrs, '') as mrmrs,
isnull(person.initials, '') as initials, isnull(person.title, '' ) as
title, isnull(person.firstname, '') as firstname,
isnull(person.lastname, '') as lastname, isnull(phone.phone, '') as
phonenr, isnull(phone1.phone, '') as ext, isnull(phone2.phone, '') as
mobilenr, isnull(email.email_address, '') as emailaddress from
crm5.contact contact left join crm5.address address on
contact.contact_id = address.owner_id and address.atype_idx = 2 left
join crm5.person person on contact.contact_id = person.contact_id left
join crm5.phone phone on contact.contact_id = phone.owner_id and
phone.ptype_idx = 1 left join crm5.phone phone1 on person.person_id =
phone1.owner_id and phone1.ptype_idx = 16385 left join crm5.phone
phone2 on person.person_id = phone2.owner_id and phone2.ptype_idx =
16389 left join crm5.email email on person.person_id = email.person_id
and email.rank = 1 where contact.[name] like '%"+tbSearchC.Text+"%'
order by contact.[name], firstname, lastname";
DataSet ds_findC = new DataSet();
SqlDataAdapter AdapterC = new SqlDataAdapter(sc_findC);
AdapterC.Fill(ds_findC);
dgDataC_search.DataSource = ds_findC;
dgDataC_search.DataBind();
}
}
}

====The Stored pdocedure i want to use in my C# to eliminate the long
sql query in the code===

CREATE PROCEDURE SearchCompany
@CompanyName varchar(50)
AS

select contact.contact_id,
contact.[name],
isnull(address.city, '') as city,
isnull(person.mrmrs, '') as mrmrs,
isnull(person.initials, '') as initials,
isnull(person.title, '' ) as title,
isnull(person.firstname, '') as firstname,
isnull(person.lastname, '') as lastname,
isnull(phone.phone, '') as phonenr,
isnull(phone1.phone, '') as ext,
isnull(phone2.phone, '') as mobilenr,
isnull(email.email_address, '') as emailaddress
from crm5.contact contact
left join crm5.address address on contact.contact_id =
address.owner_id and address.atype_idx = 2
left join crm5.person person on contact.contact_id = person.contact_id
left join crm5.phone phone on contact.contact_id = phone.owner_id and
phone.ptype_idx = 1
left join crm5.phone phone1 on person.person_id = phone1.owner_id and
phone1.ptype_idx = 16385
left join crm5.phone phone2 on person.person_id = phone2.owner_id and
phone2.ptype_idx = 16389
left join crm5.email email on person.person_id = email.person_id and
email.rank = 1
where contact.[name] like '%' + @CompanyName + '%'
order by contact.[name], firstname, lastname
GO

[Leon Friesema]

On 6 Mar 2006 05:40:19 -0800, "Simon" <s.*******@gmail.com> wrote:

I have a problem using a stored procedure in C#. Im using SQL2000 and
C# with web parts.

Currently i put the entire sql query in the CommandText. Then it works
correctly and gives the companynames in my column. As u can see below
in the C# code.

I made a Stored Procedure from the query and want to call that instead,
it saves a lot of code in my project.

What lines do i have to change, and how, to make this Stored Procedure
work and give the same output as my sql string in the code does now.

=======C# code with long SQL query in it========
[code]}

====The Stored pdocedure i want to use in my C# to eliminate the long
sql query in the code===

[STORED PROC]

Simon,

First you create a command: (where con = an open connection)

SqlCommand oCommand = new SqlCommand(CommandName, con);
oCommand.CommandType = CommandType.StoredProcedure;

Then you throw in your parameter:

SqlParameter NewParam = Command.Parameters.Add("@CompanyName",
SqlDbType.VarChar, 50, Value)
NewParam.Value = "%"+tbSearchC.Text+"%";

Then you select the output:
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = oCommand;
DataSet set = new DataSet("return");
adap.Fill(set);
oCommand.Connection.Close();

Your dataset returned is your "ds_findC"

Leon

[Simon]

Ok i followed your directions and came up with the following, i got 2
minor errors on one line:
private void btnZoekC_Zoekopdracht(object sender, System.EventArgs e)
{
string sqlString ="Persist Security Info=False;Integrated
Security=SSPI;initial catalog=SIX;server=(local)";
SqlConnection sqlCon = new SqlConnection(sqlString);
sqlCon.Open();

SqlCommand sc_findC = new SqlCommand("SearchCompany", sqlCon); //is
the input SearchCompany ok here, didnt realy understand what u mean
with CommandName.

sc_findC.CommandType = CommandType.StoredProcedure;

SqlParameter NewParam =
Command.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50, Value);
//I got the following errors on this line: The type or namespace name
'Command' could not be found (are you missing a using directive or an
assembly reference?)
// And: The name 'Value' does not exist in the class or namespace
'ZoekContact.WebPart1'
//Which assembly reference do i have to add for the 'Command' and
'Value' message the disappear.

NewParam.Value = "%"+tbSearchC.Text+"%";

SqlDataAdapter AdapterC = new SqlDataAdapter();
AdapterC.SelectCommand = sc_findC;
DataSet ds_findC= new DataSet("return");
AdapterC.Fill(ds_findC);
sc_findC.Connection.Close();
}

[Simon]

Tnx man i got it working now, cause of your great directions!

tnx