تبدیل Sqlcommand ها به SP

[hjran abdpor]

با سلام .
دو سه سال پیش یه وب سایت نوشتیم که اکثر کدهای مربوط به دیتابیسش به صورت SqlCommand نوشته شده !! حالا میخام ابم کدها را به SP تبدیل کنم و از DataSet استفاده کنم !!! بعضی از کدها را تبدیل کردم به Sp حالا اگه کسی بتونه کمکمون کنه ممنون میشم !!!! نمونه اش را میزام مثلا اینو تبدیل کنیم به SP.

DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("SELECT BookCode, RqstCount FROM Request WHERE (RqstCode = " +
e.CommandArgument.ToString() + ")", connect);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
DataTable dt1 = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter("SELECT Scale FROM BookScale WHERE (BookCode = " +
dt.Rows[0]["BookCode"].ToString() + ")", connect);
da1.Fill(dt1);
if (dt1.Rows.Count > 0)
{
if (Convert.ToInt32(dt1.Rows[0]["Scale"]) > Convert.ToInt32(dt.Rows[0]["RqstCount"]))
{
try
{
SqlCommand Cmd = new SqlCommand("UPDATE Request SET Status = 1 WHERE (RqstCode = " +
e.CommandArgument.ToString() + ")", connect);
connect.Open();
Cmd.ExecuteNonQuery();
connect.Close();
GridView1.DataBind();
}
catch (Exception ex)
{
Session.Add("Msg_", "در روند ثبت اطلاعات اشکال زیر به وجود آمده است: <br>" + ex.Message);
Response.Redirect("~/ShowMessage.aspx");
}
}
else
{
Label14.Text = "تعداد درخواست بیشتر از تعداد موجود می باشد. نمیتوانید آن را ارسال کنید";
}



یا این :

if (FirstRadioButton.Checked == true)
SqlDataSource1.SelectCommand = "SELECT BookInfo.BookCode, BookInfo.Name, BookInfo.Writers, " +
"BookInfo.Translator, BookInfo.Price, BookScale.Scale, TempSubjects.Subject, (SELECT Pic_ FROM " +
"BookPic WHERE (BookCode = BookInfo.BookCode)) AS Pic_ FROM BookInfo INNER JOIN BookScale ON " +
"BookInfo.BookCode = BookScale.BookCode INNER JOIN TempSubjects ON BookInfo.SubjectCode = " +
"TempSubjects.SubjectCode WHERE (BookInfo.Name LIKE '" + BookNameTextBox.Text + "%')";
else
{
if (InRadioButton.Checked == true)
SqlDataSource1.SelectCommand = "SELECT BookInfo.BookCode, BookInfo.Name, BookInfo.Writers, " +
"BookInfo.Translator, BookInfo.Price, BookScale.Scale, TempSubjects.Subject, (SELECT Pic_ FROM " +
"BookPic WHERE (BookCode = BookInfo.BookCode)) AS Pic_ FROM BookInfo INNER JOIN BookScale ON " +
"BookInfo.BookCode = BookScale.BookCode INNER JOIN TempSubjects ON BookInfo.SubjectCode = " +
"TempSubjects.SubjectCode WHERE (BookInfo.Name LIKE '%" + BookNameTextBox.Text + "%')";
else
{
if (NoneRadioButton.Checked == true)
SqlDataSource1.SelectCommand = "SELECT BookInfo.BookCode, BookInfo.Name, BookInfo.Writers, " +
"BookInfo.Translator, BookInfo.Price, BookScale.Scale, TempSubjects.Subject, (SELECT Pic_ FROM " +
"BookPic WHERE (BookCode = BookInfo.BookCode)) AS Pic_ FROM BookInfo INNER JOIN BookScale ON " +
"BookInfo.BookCode = BookScale.BookCode INNER JOIN TempSubjects ON BookInfo.SubjectCode = " +
"TempSubjects.SubjectCode WHERE (BookInfo.Name LIKE '" + BookNameTextBox.Text + "')";
}
}



ممنونم .

[fakhravari]

برای راحتی هر کدوم را به عنوان یه تابع تعریف کنید برای مثال
SqlDataAdapter da = new SqlDataAdapter("SELECT BookCode, RqstCount FROM Request WHERE (RqstCode = " +
e.CommandArgument.ToString() + ")", connect);

در 1 تابع بنویسید. و بعدیاشم به همین ترتیب.
تبدیل SELECT BookCode, RqstCount FROM Request WHERE RqstCode میشه
Create PROCEDURE [Serch_ID]
(
@RqstCode varchar
)
AS
SELECT BookCode, RqstCount FROM Request WHERE (RqstCode = @RqstCode)
________________
public DataTable GetList(string txt)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("Serch_ID", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add("@LinkID", SqlDbType.NVarChar).Value = txt;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

da.Fill(dt);
con.Close();

return dt;
}

[fakhravari]

شما 3 تا FirstRadioButton و InRadioButton و NoneRadioButton دارید که میتوانید مثل مثال بالا تابعی درست کنید و select روش پیاده کنید.
یه مثال برای پیاده کردن یه select با استفاده از where
public string Title;
public string Abstract;
public string Contents;
public string AddDate;
public int NewsID;
public string photoFilePath;

public string lasteror;

string ConnectionString = DAL.ConnectionString;
public DataTable GetOne(int NewsID)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("News_GetOne", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add("@NewsID", SqlDbType.Int).Value = NewsID;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

da.Fill(dt);
con.Close();

if (dt.Rows.Count > 0)
{
this.Title = dt.Rows[0]["Title"].ToString();
this.Abstract = dt.Rows[0]["Abstract"].ToString();
this.Contents = dt.Rows[0]["Contents"].ToString();
this.AddDate = dt.Rows[0]["AddDate"].ToString();
this.NewsID = Int32.Parse(dt.Rows[0]["NewsID"].ToString());
this.photoFilePath = dt.Rows[0]["img"].ToString();
}
else
{
lasteror = "موردی یافت نشد";

}
return dt;
}
create procedure [dbo].[News_GetOne](@NewsID int)
as
begin
SELECT NewsID, Title, Abstract, Contents, AddDate, img FROM TBLNews where NewsID=@NewsID
end

[fakhravari]

برای update

public string Title;
public string Abstract;
public string Contents;
public string AddDate;
public int NewsID;
public string photoFilePath;

public string lasteror;

string ConnectionString = DAL.ConnectionString;
public void UpdateRecord(string Title, string Abstract, string Contents, string AddDate, string img, int NewsID)
{
try
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("News_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();

cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = this.Title;
cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = this.Abstract;
cmd.Parameters.Add("@Contents", SqlDbType.NVarChar).Value = this.Contents;
cmd.Parameters.Add("@AddDate", SqlDbType.NVarChar).Value = this.AddDate;
cmd.Parameters.Add("@img", SqlDbType.NVarChar).Value = this.photoFilePath;
cmd.Parameters.Add("@NewsID", SqlDbType.Int).Value = this.NewsID;

cmd.ExecuteNonQuery();
con.Close();
}
catch
{
lasteror = "اشکال در ثبت اطلاعات";
}
}


create procedure [dbo].[News_Update](@Title nvarchar(max), @Abstract nvarchar(max), @Contents nvarchar(max), @AddDate nvarchar(max), @img nvarchar(max), @NewsID int)
as
begin
UPDATE [TBLNews] SET [Title] = @Title, [Abstract] = @Abstract, [Contents] = @Contents, [AddDate] = @AddDate, [img] = @img WHERE [NewsID] = @NewsID
end