The Insides Of The SqlConnection, SqlCommand, And SqlDataReader, Objects
Mar 22, 2008
Im just curious, can anyone explain what exactly the methods do within the objects (sqlconnection, sqlCommand, SqlDataReader) . and why it takes three objects to carry out a connection. Php only used one object I believe.
But most of all i would like to know how they work. like sqlCommand carrys the connection (sqlcommand.connection) while sql connection retrieves the address connection (sqlconnection(connectionstring)). what does Sqlconnection do once it gets this address. and what does it give sqlcommand when its ready to take out the query and put it in the reader once the sqlconnection.open() to make it simple. let me put it this way
sqlConnection con = new SqlConnection(_connectionstring); // server address comes through here. along with security and database name what exactly does the object con do ? it runs through the constructor and does what ?
SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //So now what ever the SqlConnection did with the _connectionstring. it now passes it through the connection property. the connection property does something with value of con. what is it ??
cmd.CommandText = 'select, ID, Name, Price.....' // I think i know what that does
con.Open() // I know that the connection opens here. but what exactly does this method do to make it open ????????
sqlDataReader reader = cmd.ExecuteReader(); im some what confused with this one. sqldataReader reader was never declared and its not even a static method. What exactly is it ????? cmd.ExecuteReader() now takes the property connection which did something with the con value. then it used the commandtext property to request the query. it takes con.open does something and now its execute reader. Im a lil confused
last but not least, con.close() what does that do ?? its alot but can anyone fill me in. or should i say fill(me); in lol, get it. never mind.
Hi. I want to now a little about CodeBehind how to use it with SqlConnection, SqlCommand and SqlDataReader. How i use:Web.Config <connectionStrings><add name="ConnectionStringTest" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DSNbase.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/></connectionStrings> Default.aspx<asp:Repeater ID="test" runat="server" DataSourceID="SqlDataSourcetest">.....<asp:SqlDataSource ID="SqlDataSourcetest" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"ProviderName="<%$ ConnectionStrings:ConnectionStringTest.ProviderName %>" SelectCommand="SELECT [ID], [Name], [Sex], [Born], [Color], [Dog], [Images] FROM [Dogs] WHERE ([Dog] = 'YES') ORDER BY [ID]"></asp:SqlDataSource> How can i take my asp:SqlDataSource and put it in my CodeBehind, so it use the ConnectionString from my Web.Config and so i can use my Repeater on my default.aspx site.. !?Maybe if u got a great link that show "How to do" maybe om MSDN/MSDN2.
Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott
Hi Just a doubt: s it possible to write an ado.net code that uses a sqldataadpter object and a sqlcommand object ( BOTH OBJECTS, IS IT POSSIBLE?) to retrieve data from the database by calling a stored procedure. Thanks a lot
Hello All, I am new to data access and i have got the problem to display the data into the page by binding the gridview with sqlConnection, sqlCommand and sqlDataReader objects. The actually code is written as:protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) {SqlConnection myConnection; SqlCommand myCommand;SqlDataReader myReader; myConnection = new SqlConnection();myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["LatteConnectionString"].ConnectionString; myCommand = new SqlCommand();myCommand.CommandText = "select * from AntiVirusVendors";myCommand.CommandType = CommandType.Text; myCommand.Connection = myConnection; myCommand.Connection.Open();myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); GridView1.DataSource = myReader; GridView1.DataBind(); myCommand.Dispose(); myConnection.Dispose(); }
}
and the GridView in html is listed as: <div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </div> So the problem is ---> there is nothing shown in the page, no errors no anything.... just the empty page. Any ideas would be appreciated. Thanks in advance! Joe
I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.
Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3. Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them) Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)
I have a sql server project where I have added 1 stored procedure (named StoredProcedure1) and 1 class (named MyClass). MyClass has two functions Func1() and Func2(). The stored procedure simply instantiates MyClass and calls Func1().
Func1() creates a SqlConnection object using "context connection=true" - opens the connection and then calls Func2(). Func2() also creates a SqlConnection object using "context connection=true" - and attempts to open the connection. However when attempting to the Open() call inside Func2(), the code just exits. What gives? The code is pasted below, thanks!
*******THE STORED PROCEDURE************** using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using SqlServerProject1;
public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedure1() { // Put your code here MyClass obj = new MyClass(); obj.Func1(); obj = null; } };
*******THE CLASS "MYCLASS"************** using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public class MyClass { public MyClass() {
}
public void Func1() { using ( SqlConnection conn = new SqlConnection("context connection=true") ) { conn.Open();
Func2(); conn.Close(); } }
public void Func2() { using ( SqlConnection conn = new SqlConnection("context connection=true") ) { if ( conn.State != ConnectionState.Open ) conn.Open();
if ( conn.State == ConnectionState.Open ) conn.Close(); } } }
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: { Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); Line 26: mySqlDataAdapter.Fill(myDataSet); Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System; using System.Data; using System.Data.SqlClient ; using System.Configuration; using System.Collections; using System.Text; 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.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page { protected void Page_Load(object sender, DataSet myDataSet, EventArgs e) { string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password"; string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString)) { SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); mySqlDataAdapter.Fill(myDataSet); return myDataSet; }
// Create a new XmlTextWriter instance XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
Hi guys. I'm having trouble declaring an sqlcommand. What I want to do is declare a global sqlcommand and I would want this sqlcommand to vary depending on the conditions on my page_load. Here's the code....
Dim p_s_syounin2 As New SqlCommand Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then p_s_syounin2 = ("INSERT INTO (p_s_syounin2) SELECT syain_hnm FROM TR_syainID WHERE TR_syainID.syozokubu_id=20 AND TR_syainID.syozokuka_id=21 AND TR_syainID.kaikyuu_id=23, cnn") '''' THIS DOES NOT WORK! End If End Sub p_s_syounin2 .ExecuteNonQuery()
What is the correct way of declaring p_s_syounin2? Thanks.
Is it ok to re-use a SqlCommand object? In a method, I am executing 2 separate parameterized sql statements. Before I run the second, I will clear the command objects parameters.(command.parameters.clear()) I'm just checking to see if it is good coding practice or not. thanks,SC
I created an xmldocument which I would like to insert in a db field with the data type XML.The following code is giving me the error:System.Data.SqlClient.SqlException: XML parsing: line 1, character 38, unable to switch the encoding SqlCommand cmdUpdate = new SqlCommand("sp_AddHistory", sqlConnection); cmdUpdate.CommandType = CommandType.StoredProcedure; cmdUpdate.Parameters["@FieldsChanged"].Value = xmlDoc.innerXML; // don't know whether this is good cmdUpdate.ExecuteNonQuery(); innerXML:<?xml version="1.0" encoding="utf-8"?><Fields> <Field> <FieldName>comp_Area</FieldName> <OldValue>Area 52</OldValue> <NewValue>Area 51</NewValue> </Field></Fields> The XML seems fine.. any ideas?
Hi! I need to know what can I do to use a variable in the WHERE condition of the sqlcommand as I show you: current_user = User.Identity.Name
Dim cmd As New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = current_user", cn) Obviously it doesn't work and I need your help. Thanks.
Hello, I am trying to insert a value into a specific row in a table. The error comes from the myCommand2 statement but i don't know how to solve it. Please help! My code is as follows:SqlCommand myCommand = new SqlCommand("select count(*) from updatetable", myConnection);
myReader = myCommand.ExecuteReader(); while (myReader.Read()) int count = myReader.GetInt32(0); SqlCommand myCommand2 = new SqlCommand("insert into interface (Total) where description = 'Graphicads' value ( " + count + ")", myConnection2);myCommand2.CommandType = CommandType.Text; myCommand2.ExecuteNonQuery();
Hello all, when I write my CommandText, It displays it as insert into videos values (127.0.0.1, 4000, 434), which the ip address is sent int as a string, put i keep getting an error, and I think it is because it is wanting an string, but it is sending in these "." or something, anyways heres my method, I dont know how I can add " " around the ip when i pass it in, since it is a string. thanks. 1 [WebMethod] 2 public void Register(string ip, int port, int[] handles) 3 { 4 SqlConnection dbConn = new SqlConnection(connStr); 5 SqlCommand comm = new SqlCommand(); 6 comm.Connection = dbConn; 7 dbConn.Open(); 8 for (int i = 0; i < handles.Length; i++) 9 { 10 comm.CommandText = "insert into videos values (" + ip + ", " + port + ", " + handles[i] + ")"; 11 comm.ExecuteNonQuery(); 12 } 13 dbConn.Close(); 14 }
Using SqlCommand, this is how I am updating a database table:
Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs) strSQL = "UPDATE Basket SET Quantity = Qty, Total = TotAmt WHERE BasketID = BID AND ProductID = PID" sqlCmd = New SqlCommand(strSQL, sqlConn) With sqlCmd .Parameters.Add("Qty", SqlDbType.Int).Value = CInt(iQty) .Parameters.Add("TotAmt", SqlDbType.Money).Value = CInt(iQty) * CType(ea.Item.FindControl("lblPrice"), Label).Text .Parameters.Add("BID", SqlDbType.VarChar, 50).Value = strBasketID .Parameters.Add("PID", SqlDbType.VarChar, 50).Value = CType(ea.Item.FindControl("lblID"), Label).Text End With sqlConn.Open() sqlCmd.ExecuteNonQuery() sqlConn.Close()End Sub
But the above code generates the following error pointing to the red colored line in the above code: Invalid column name 'BID'.Invalid column name 'PID'. BID & PID are not the column names in the actual database table but can't it be done in the way I have done above? In fact, Qty & TotAmt are not the column names in the actual database table as well; so why isn't the error pointing to Qty & TotAmt as they will be evaluated before BID & PID, if I am not mistaken?
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
How to test @au_lname's value sends to the following following sql command? Dim MyCommand As New SqlCommand("UPDATE [authors] SET [au_lname] = @au_lname", MyConnection)MyCommand.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.NVarChar)).Value = me.au_lname.text I tried to print the "MyCommand.CommandText.ToString" but only get UPDATE [authors] SET [au_lname] = @au_lname with no value in the command text. Thanks!
Hi: I am using sqlcommand.parameters.add() and up to 74 parameters for one sqlcommand, it gets the error "too many parameters for the sqlcommand", I wonder if someone know is there limitation of the paramters that I can pass to sqlcommand? If so, how many parameters I can pass to the sqlcommand at one time? thank u in adv.
Hello,I was trying to do the following: Dim cmd As SqlCommand Dim objConnection As SqlConnection objConnection = New SqlConnection = Web.configwhere the We.config is where my connection string is set. but I get a sintax error in the Web.config line.is it possible to asign the value of the web.config content to the new sql connection?thanks for any suggestions.
Hi.. Every time I want to read any record from data base I read it in dataset for example:SqlConnection con = new SqlConnection(@"Data Source=localhost ;Initial Catalog=university ;Integrated Security=True"); SqlCommand cmd = new SqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text + "' and [UserPassword]='" + TextBox2.Text + "' ", con);SqlDataAdapter adp = new SqlDataAdapter(); adp.SelectCommand = cmd;DataSet ds = new DataSet(); adp.Fill(ds, "UserID");foreach (DataRow dr in ds.Tables["UserID"].Rows) { user_type = dr[0].ToString(); Session.Add("User_AuthorityID", user_type); ......... Is there easier way to read data from data base? How I can use SqlDataReader to do that? Thanks..
Hey All, I have come across a situation with two tables, they are dynamic and the user can add and edit values in the tables so I need to build a dynamic display control. It is all based around an FAQ system I have built. The user can create new FAQ categories (that is one table) then create a new FAQ Question & Answer (that is the second table). The tables are linked by the category id. So now I am trying to display the FAQ section like so. CATEGORY NAME QuestionAnswerQuestionAnswerCATEGORY NAME QuestionAnswerQuestionAnswerCATEGORY NAME QuestionAnswerQuestionAnswer So my idea was to run a loop within a loop. First loop the category name, then within the category name, loop a second time to grab all of the questions & answers within the category id captured from the first loops sql select statement, then proceed to loop the category name again and of course repeat the process until all loops are completed. However I am getting, and I kinda figured I would get an error about my SQLDataReader. Bellow is my code maybe some type of edit or different recommendation is needed. Any help will do, thanks!Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load '--- Database Connection ---Dim sConnStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Dim cnBKPost As New SqlConnection(sConnStr) '--- End DB Connection ---- '----- FAQ's ------- Dim sql As String = "SELECT category_id, category_name FROM faq_category ORDER BY category_name DESC"Dim cmd As New SqlCommand(sql, cnBKPost) cnBKPost.Open()Dim reader As SqlDataReader = cmd.ExecuteReader() Dim str As New StringBuilder() Dim catid As IntegerDo While reader.Read() '--- Category Titles ----catid = reader("category_id") str.Append("<h2>" & reader("category_name") & "</h2>") '--- End Category Title --- '--- Get FAQ's In Category --- Dim sqlcat As String = "SELECT faq_question, faq_answer FROM tbl_faq WHERE faq_category = '" & catid & "'"Dim cmdcat As New SqlCommand(sqlcat, cnBKPost) Dim readerfaq As SqlDataReader = cmdcat.ExecuteReader()Do While readerfaq.Read() str.Append("<p><font style='font-size:12pt;font-color:#daa520;>'" & reader("faq_question") & "</font><br />")str.Append(reader("faq_answer") & "</p>") str.Append("<br /><br /><br />") Loop readerfaq.Close() '--- End Get FAQ's in Category --- Loop reader.Close() cnBKPost.Close()Me.Literal1.Text = str.ToString() End Sub End Class
i'm using c# and SqlDataReader to simply retrieve data from one column of a database. problem is it's an integer i'm trying to retrieve, and so i'm trying to put it into an int variable, and i get the error "CS0029: Cannot implicitly convert type 'object' to 'int'" . i've looked for an answer for about an hour and every example for the SqlDataReader that i can find deals with strings only or the examples are too complex for me to understand.
there must be an easy way to retrieve this data and put it into an integer! help...
my line of code that creates the error:
int intGuestNum = dtrSelectTotalSessions["online_numSessions"];
I am trying to add a DateTime? parameter to SqlCommand. It works when the variable has a value, but when its null, an exception gets thrown saying that parameter was not supplied.What is causing this error?
With VS2005, there is a new component SqlDataSource, <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource> Then you can assign SP and bind datasource to a get data for this component in .NET code:SqlDataSource1.SelectCommand = "spName"SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure SqlDataSource1.ConnectionString = Comm.connString There is no way to set sqlcommand timeout for this stored procedure like SqlClient.SqlCommand. How can I do this?
Hi, I've come from desktop to web (say no more?) I'd like to fire a sqlCommand.StatementCompleted event in ASP.NET & use the sender argument to retrieve the output of a stored proc. The command is in a button event. The event handler += is also set up in the same button event. Naturally the event handler proc is separate as its own procedure In the event handling procedure, I cast the sender arg. to an SQLCommand object & try to extract the value of the output parameter. When I try to do this I get a "No reference set to object" error raised in . Is this because there was a post back after the command completed & the Command object lost its state? If so, what would be the best way to retain the parameters value? Using session state or Profile? Any help would be greatly appreciated below is cut down code as to what I'm doing:protected void Button1_Click(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand(); cmd.StatementCompleted += new StatementCompletedEventHandler(c_StatementCompleted); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); } void c_StatementCompleted(object sender, StatementCompletedEventArgs e) { // get an error here string result = ((SqlCommand)sender).Parameters["returnPrm"].Value.ToString(); }
