|
Hello all.
I am interested in hearing about other's experiences with CLR Stored Procedures (C#, VB.NET, whatever) in SQL 2005. Positive, negative, no difference. Better with single-tables than with large joins. Techniques and optimizations. What worked and did not work and why. What things they are better for than others (i.e. heavy mathematical or string operations). Etc.
I am also posting a small message in the C# forum to see if any developers over there have experience and would like to come over here to share.
I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs. But rather than go the shotgun approach and rewrite everything, I want to get the best bang for my buck, and figured starting here would be a good idea.
Thanks!
Peace!
-=- James Please rate this message - let me know if I helped or not!<hr></hr>If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles
|
|
|
|
|
James R. Twine wrote: I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs.
Not sure I understand your question. SQL Server (and others) have supported developing stored procedures using external languages like C/C++ for many years now. What differentiation are you making between that and using a CLR based language for developing stored procedures?
led mike
|
|
|
|
|
I know about Extended Stored Procedures and have even written/used them in the past.
CLR-based SPs run in the .NET environment, and even though it is hosted by SQL Server, they can be considered more stable because errors/exceptions cannot cause instabilities in the XP server's executable (stepping on its memory, for example). This is one reason why they may be more attractive, especially if deploying them to a server that is used for other purposes at a client site.
They also may work faster (other than the initial compilation hit) on systems that would normally run a XSP in a separate process because the cross-process overhead is eliminated (somewhat). CLR-based objects can be used as UDTs.
Anyway, the part of my post that you quoted was miswritten - of course I can optimize some of my operations. The question is more along the lines of what KINDS of operations are likely to be sped up by moving to a CLR-based SP. I am not trying to compare the performance of XSPs vs. CLR-SPs.
Peace!
-=- James Please rate this message - let me know if I helped or not!<hr></hr>If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles
|
|
|
|
|
CLR based code may/will be useful for the areas where TSQL is not optimised; that is procedural code, string handling and complex calculations. You also have access to .NET Framework classes for registry, files, XML etc.
Comparing the performance of XSPs and CLR is probably moot anyway since XSPs are deprecated in SQL 2005 onwards.
|
|
|
|
|
I've used CLR integration for several cases and I have to say that I have totally discarded using plain C so my answer may be a bit biased  .
I like to use normal T-SQL procedures and plain SQL especially when transactional processing is needed and/or when working with result sets. T-SQL performs better in these areas. What I typically do, is that I only 'extend' T-SQL with C# based functionality, not replacing it. Some of the usages have been:
- complex mathematical functions not implemented in T-SQL. Using these for columns in result sets
- integration to the database server or even to the client calling the db
- file handling capability for stored procs (and also logging)
- extended string handling (T-SQL is actually very lousy in this) etc.
The negative aspects I see are:
- integration to the actual connection calling CLR based functionality. It's working, but for some reason not very intuitive (at least not for me)
- installation is a bit difficult if it's meant to be done by client. Creating a full silent install script for the whole database server requires more work than I originally expected
- cannot use fibers so in some installations it may cause performance problems on other areas of database usage
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Suppose a left table name is: tblstudent. Which has two column , studentID(KEY) and studentName. On the other hand right table name is tblMarks. In this first column is studentID(key) and second column is ‘Division’ of type bit. The student who got First class , with his respective name , Division value is entered 1.But student, who did not get First class, not entered in the table. Now what should be the query that presents first column all student name from left table(tblstudent) and second column Division with respect to name. Student who not get first class present as null value.
|
|
|
|
|
You are joking of course. Why would anyone want to do your homework for you? Without even a please?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
To prevent that he understands what he is doing, in order to prevent him from getting a degree. If that fails, one of us might end up maintaining that code..
So, by all means, let's just answer all homework-questions
--edit--
Answers only, off course! Explanation of an answer wouldn't help. If one does not want to learn, then don't push an explanation. Forced learning is plain rude 
|
|
|
|
|
Tell you what, shall we set up a website to answer just homework questions - obviously, as they are students we wouldn't charge, and we could give up our paying jobs to run it? Just think of how many degress we could be responsible for globally!
Of course, as we are (more or less) in the same time zone we would have to employ people (out of our own pockets, naturally) to answer urgent (aren't they always) questions during the European night.
Good plan?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
That has been tried already, with various setups.. StackOverflow for the students, Rent-a-coder for the "urgentz codez". Aw, wait, Rent-a-coder isn't free, is it?
..the sad thing is, what you are describing is almost what we are doing now.
|
|
|
|
|
SELECT tblStudent.StudentID, tblStudent.StudentName, tblMarks.Division FROM tblStudent LEFT OUTER JOIN tblMarks ON tblStudent.StudentID = tblMarks.StudentID
|
|
|
|
|
In which database u want to run this???
following is for MS-Access
-----------------------------
SELECT [studentName], [Division]
FROM tblstudent LEFT JOIN tblMarks ON [tblstudent].[studentID]=[tblMarks].[studentID];
"We can't solve problems by using the same kind of thinking we used when we created them"
|
|
|
|
|
just grab a chair, i will post you the entire code in a few minutes ok? 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
i m using Visual studio 2005.here i am having a problem that i have a RadioButtonList which retrives data from sql datasource. it displays the Items correctly.Now when i try to display the SelectedItems on a label i get an error.kindly help me m stuck..m a student a really new to it.please help..thanx a lot...
|
|
|
|
|
Ok, a couple of things
1. This isn't a sql question, its either vb.net or c#, depending on what you are using - you state the data is being retrived correctly, so there is no sql problem
2. With no code and no error message how can anybody work out what is wrong?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Its hard to help when one has no idea what kind of error you are getting. Please paste the line that you are using to display the SelectedItems on the label or the error. It could be something minor that you are not taking into consideration.
|
|
|
|
|
the error i am getting on browser page is :-
"Server Error in '/Website_dtc' Application.
--------------------------------------------------------------------------------
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
"
the RadiobuttonList is databound as follows-
"RadioButtonList1.DataSource = ds.Tables["temp1"];
RadioButtonList1.DataTextField = "buses";
RadioButtonList1.DataValueField = "buses";
RadioButtonList1.DataBind();
"
the RadiobuttonList Item is selected as:-
" protected void Button1_Click(object sender, EventArgs e)
{
lbltemp.Text = RadioButtonList1.SelectedItem.Text;
}
"
|
|
|
|
|
At a guess, either
a) you haven't selected anything in the list
or
b) you rebind the list each time the page loads, thus overwriting the selection... Might want to have a look at the IsPostBack page variable.
|
|
|
|
|
dear paddy ..
this is my Source Code for the Page--
"<%@ Page Language="C#" AutoEventWireup="true" CodeFile="searchstops_1.aspx.cs" Inherits="searchstops_1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Design by Free CSS Templates
http://www.freecsstemplates.org
Released for free under a Creative Commons Attribution 2.5 License
Name : Monoblock
Description: A two-column, fixed-width design with dark color scheme.
Version : 1.0
Released : 20080418
-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>DTC_Services</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<link href="style.css" rel="stylesheet" type="text/css" media="screen" />
<script language="javascript" type="text/javascript">
<!--
function content_onclick() {
}
// -->
</script>
</head>
<body>
<form id="form1" runat="server">
<div id="header" style="width: 1202px">
<div id="logo">
</div>
<br />
<br />
<br />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large" ForeColor="Black"
Style="z-index: 100; left: 39px; position: absolute; top: 23px" Text="DELHI TRANSPORT CORPORATION"></asp:Label>
<br />
<asp:Image ID="Image2" runat="server" ImageUrl="~/PICS/logo1.gif" Style="z-index: 101;
left: 1014px; position: absolute; top: 31px" />
<br />
<br />
<!-- end #search -->
<br />
</div>
<!-- end #header -->
<div id="menu" style="width: 1202px">
<br />
<strong><span style="color: #ffffcc">
<span style="font-size: 14pt; font-family: Verdana"><a href="home.aspx">Home </a>
<a href="home.aspx">Services Offered </a>
<a href="home.aspx">Apply for DTC pass</a> <a href="home.aspx">Contact
Us</a> <a href="home.aspx">Help </a></span><span style="font-size: 14pt">
</span></span></strong></div>
<!-- end #menu -->
<div id="page" style="width: 1121px; height: 635px">
<!-- end #content -->
<div id="sidebar" style="height: 690px">
<ul>
<li>
<h2>
buses at a glance</h2>
<p>
</p>
<p>
</p>
<p>
<asp:Image ID="Image1" runat="server" ImageUrl="~/PICS/2008053054180501.jpg" Style="z-index: 100;
left: 914px; position: absolute; top: 370px" Height="126px" />
</p>
</li>
<li id="calendar">
<h2>
Services offered</h2>
<div id="calendar_wrap">
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
</div>
</li>
</ul>
</div>
<div style="clear: both;">
<div class="entry">
<asp:HyperLink ID="HyperLink4" runat="server" NavigateUrl="~/home.aspx" Style="z-index: 100;
left: 924px; position: absolute; top: 733px" Width="208px">Buses through a stoppage</asp:HyperLink>
<asp:Panel ID="Panel1" runat="server" Height="691px" Style="z-index: 105; left: 46px;
position: absolute; top: 255px" Width="823px">
<br />
<br />
<span style="font-size: 16pt">Services offered >><br />
<span style="font-size: 14pt">
Search Buses by Stops<br />
<asp:Label ID="lblsrc" runat="server" Font-Bold="False" ForeColor="Green" Style="z-index: 100;
left: 580px; position: absolute; top: 300px"></asp:Label>
<asp:Label ID="lbldes" runat="server" Font-Bold="False" ForeColor="Green" Style="z-index: 101;
left: 153px; position: absolute; top: 305px"></asp:Label>
<br />
<asp:Label ID="Label2" runat="server" Font-Italic="True" Font-Size="Smaller" ForeColor="Red"
Style="z-index: 102; left: 348px; position: absolute; top: 128px" Text="You Selected"></asp:Label>
<br />
<span style="font-size: 12pt; color: #ff0000"><em>
<asp:Label ID="Label4" runat="server" Font-Bold="True" Font-Underline="True" Style="z-index: 103;
left: 155px; position: absolute; top: 230px" Text="SOURCE"></asp:Label>
<%--<asp:RadioButtonList ID="RadioButtonList1" runat="server" Style="z-index: 104; left: 344px;
position: absolute; top: 476px" OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged">
</asp:RadioButtonList>--%>
<br />
<br />
<asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Underline="True" Style="z-index: 104;
left: 580px; position: absolute; top: 226px" Text="DESTINATION"></asp:Label>
<br />
<br />
</em></span>
<span style="font-size: 12pt; color: #cc0000"><strong>
<asp:Label ID="Label5" runat="server" Font-Size="Smaller" Style="z-index: 105; left: 264px;
position: absolute; top: 410px" Text="Following Bus Routes are available"></asp:Label>
<asp:RadioButtonList ID="RadioButtonList1" runat="server" Style="z-index: 106; left: 333px;
position: absolute; top: 467px" AutoPostBack="True" OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged">
</asp:RadioButtonList>
<asp:Label ID="lbltemp" runat="server" Style="z-index: 108; left: 628px; position: absolute;
top: 462px"></asp:Label>
</strong></span></span></span></asp:Panel>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<asp:HyperLink ID="HyperLink5" runat="server" NavigateUrl="~/home.aspx" Style="z-index: 102;
left: 929px; position: absolute; top: 864px" Width="204px">Apply for DTC pass</asp:HyperLink>
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/home.aspx" Style="z-index: 103;
left: 926px; position: absolute; top: 800px" Width="210px">Search by Bus No.</asp:HyperLink>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/home.aspx" Style="z-index: 104;
left: 928px; position: absolute; top: 662px" Width="211px">Search Buses by Stops</asp:HyperLink>
</div>
</div>
</div>
<div style="font-size: 0.8em; text-align: center; margin-top: 1.0em; margin-bottom: 1.0em;">
</div>
</form></body>
</html>
"
Here is The CODE SEGMENT
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class searchstops_1 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
SqlDataAdapter adp = new SqlDataAdapter();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["dtcConnect"].ConnectionString;
con.Open();
lblsrc.Text = Session["source"].ToString();
lbldes.Text = Session["dest"].ToString();
String str;
str = "select *from busno where exists (select 1 from busroutes bs1 where bs1.busno=busno.buses and bs1.stops='" + Session["source"].ToString() + "') and exists (select 1 from busroutes bs2 where bs2.busno=busno.buses and bs2.stops='" + Session["dest"].ToString() + "')";
adp.SelectCommand = new SqlCommand(str, con);
adp.SelectCommand.ExecuteNonQuery();
adp.Fill(ds, "temp1");
RadioButtonList1.DataSource = ds.Tables["temp1"];
RadioButtonList1.DataTextField = "buses";
RadioButtonList1.DataValueField = "buses";
RadioButtonList1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
Response.Redirect("searchstops.aspx");
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex != -1)
lbltemp.Text = RadioButtonList1.SelectedItem.Text;
}
}
"
thanx for the help
|
|
|
|
|
Thanx Paddy..my problem was solved...
|
|
|
|
|
That error will occur if you click the button when you have not selected any of the radio buttons on the RadioButtonList1. So you need to check whether the selection has first happened. Probably using:
if (RadioButtonList1.SelectedIndex != -1)
lbltemp.Text = RadioButtonList1.SelectedItem.Text;
|
|
|
|
|
I am inserting record in table using stored procedure.
CREATE PROCEDURE add_member(
@UserName Varchar( 50 ),
@Password Varchar( 50 ),
@dob datetime,
@education Varchar(50),
@designation Varchar(50),
@name Varchar(50)
) AS
If Exists( SELECT username FROM member WHERE username = @username )
RETURN -1
ELSE
BEGIN
DECLARE @ID int -- new ID value holder
INSERT member (
username, password, dob, education, designation, name
) VALUES (
@username, @password, @dob, @education, @designation, @name
)
-- get the new identity column value
SET @id = @@IDENTITY
END
GO
-------------------------------
I am using classic asp to add records in this way -
sql = "add_member " & "'" & request.form("username") & "'" & "," & "'" & request.form("password") & "'" & "," & "'2/2/2'" & "," & request.form("education") & "," & request.form("designation") & "," & "'" & request.form("name") & "'"
set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.Open sql, con
Its adding new record successfully But I am not able to read id of newly added record.
Plzzzzzzz help
|
|
|
|
|
That's because you aren't returning it. You set @ID, but you need to either set an output parameter or select @ID to return it to your code.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How to return and read in asp please explain in detail
Thanks in advance
|
|
|
|
|
You can either use an OUTPUT parameter in your query (search SQL Help for OUTPUT PARAMETER) or simply do a select and return the value in a recordset just like any other select statement does. An output parameter is more efficient.
To be honest, its so long since I used ADODB that I can't remember the syntax for retriving output parameters, but I do remeber you have to close the recordset first, strange as it seems.
Hopefully this will give you some pointers to enable you to resolve your problem after a little work on Google.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
