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 CreateChildCont rols()
{

tbSearchC = new TextBox();
btnSearchC = new Button();
btnSearchC.Text = "Find";
btnSearchC.Clic k += new EventHandler(th is.btnSearchC_S earchAction );
Controls.Add(tb SearchC);
Controls.Add(bt nSearchC);
dgDataC_search = new DataGrid();
dgDataC_search. HeaderStyle.Bor derWidth = 2;
dgDataC_search. HeaderStyle.Fon t.Bold = true;
dgDataC_search. HeaderStyle.Bac kColor = Color.LightStee lBlue;
dgDataC_search. HeaderStyle.For eColor = Color.Black;
dgDataC_search. HeaderStyle.Wra p = false;
dgDataC_search. GridLines = System.Web.UI.W ebControls.Grid Lines.Both;
dgDataC_search. SelectedItemSty le.BackColor =
System.Drawing. Color.YellowGre en;
dgDataC_search. AutoGenerateCol umns = false;
BoundColumn newColumn_Compa nyname;
newColumn_Compa nyname = new BoundColumn();
newColumn_Compa nyname.DataFiel d = "name";
newColumn_Compa nyname.HeaderTe xt = "Companynam e:";
newColumn_Compa nyname.HeaderSt yle.Width=120;
dgDataC_search. Columns.Add(new Column_Companyn ame);

Controls.Add(dg DataC_search);
}

private void btnSearchC_Sear chAction(object sender, System.EventArg s e)
{

string sqlString ="Persist Security Info=False;Inte grated
Security=SSPI;i nitial catalog=SIX;ser ver=(local)";
SqlConnection sqlCon = new SqlConnection(s qlString);
sqlCon.Open();
SqlCommand sc_findC = new SqlCommand();
sc_findC.Connec tion = sqlCon;
sc_findC.Comman dType = CommandType.Tex t;

sc_findC.Comman dText ="select contact.contact _id, contact.[name],
isnull(address. city, '') as city, isnull(person.m rmrs, '') as mrmrs,
isnull(person.i nitials, '') as initials, isnull(person.t itle, '' ) as
title, isnull(person.f irstname, '') as firstname,
isnull(person.l astname, '') as lastname, isnull(phone.ph one, '') as
phonenr, isnull(phone1.p hone, '') as ext, isnull(phone2.p hone, '') as
mobilenr, isnull(email.em ail_address, '') as emailaddress from
crm5.contact contact left join crm5.address address on
contact.contact _id = address.owner_i d and address.atype_i dx = 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_i d =
phone1.owner_id and phone1.ptype_id x = 16385 left join crm5.phone
phone2 on person.person_i d = phone2.owner_id and phone2.ptype_id x =
16389 left join crm5.email email on person.person_i d = email.person_id
and email.rank = 1 where contact.[name] like '%"+tbSearchC.T ext+"%'
order by contact.[name], firstname, lastname";
DataSet ds_findC = new DataSet();
SqlDataAdapter AdapterC = new SqlDataAdapter( sc_findC);
AdapterC.Fill(d s_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.m rmrs, '') as mrmrs,
isnull(person.i nitials, '') as initials,
isnull(person.t itle, '' ) as title,
isnull(person.f irstname, '') as firstname,
isnull(person.l astname, '') as lastname,
isnull(phone.ph one, '') as phonenr,
isnull(phone1.p hone, '') as ext,
isnull(phone2.p hone, '') as mobilenr,
isnull(email.em ail_address, '') as emailaddress
from crm5.contact contact
left join crm5.address address on contact.contact _id =
address.owner_i d and address.atype_i dx = 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_i d = phone1.owner_id and
phone1.ptype_id x = 16385
left join crm5.phone phone2 on person.person_i d = phone2.owner_id and
phone2.ptype_id x = 16389
left join crm5.email email on person.person_i d = 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.*******@gmai l.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(Comm andName, con);
oCommand.Comman dType = CommandType.Sto redProcedure;

Then you throw in your parameter:

SqlParameter NewParam = Command.Paramet ers.Add("@Compa nyName",
SqlDbType.VarCh ar, 50, Value)
NewParam.Value = "%"+tbSearchC.T ext+"%";

Then you select the output:
SqlDataAdapter adap = new SqlDataAdapter( );
adap.SelectComm and = oCommand;
DataSet set = new DataSet("return ");
adap.Fill(set);
oCommand.Connec tion.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_Zoekop dracht(object sender, System.EventArg s e)
{
string sqlString ="Persist Security Info=False;Inte grated
Security=SSPI;i nitial catalog=SIX;ser ver=(local)";
SqlConnection sqlCon = new SqlConnection(s qlString);
sqlCon.Open();

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

sc_findC.Comman dType = CommandType.Sto redProcedure;

SqlParameter NewParam =
Command.Paramet ers.Add("@Compa nyName", SqlDbType.VarCh ar, 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.We bPart1'
//Which assembly reference do i have to add for the 'Command' and
'Value' message the disappear.

NewParam.Value = "%"+tbSearchC.T ext+"%";

SqlDataAdapter AdapterC = new SqlDataAdapter( );
AdapterC.Select Command = sc_findC;
DataSet ds_findC= new DataSet("return ");
AdapterC.Fill(d s_findC);
sc_findC.Connec tion.Close();
}

[Simon]

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

tnx