----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: Ritesh_Kesharwani Message 2 in Discussion Hi go through this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm l/cpconsampleapplication.asp You will get something with this code also This page contains sample code snippets for the ADO.Net course I teach from time to time and Collin County Community College. Connection in VB.Net Dim cnNorthwind as New System.Data.SqlClient.SqlConnection() cnNorthwind.ConnectionString = _ "User ID=sa;" & _ "Password=2389;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Connection TimeOut=60;" C#: OleDbConnection System.Data.OleDb.OleDbConnection cnNorthwind = new System.Data.OleDb.OleDbConnection(); cnNorthwind.ConnectionString = "Provider=SQLOLEDB;" + "Data Source=databaseserver;" + "Initial Catalog=databasename;" + "Integrated Security=SSPI;"; Visual Basic: Creating Connection Pooling ' Connection 1 Dim myConnection as New SqlClient.SqlConnection() myConnection.ConnectionString = "User ID=sa;" & _ "Password=me2I81sour2password;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Connection TimeOut=60;" ' Connection 2 Dim myConnection as New SqlClient.SqlConnection() myConnection.ConnectionString = "User ID=sa;" & _ "Password=password;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Connection TimeOut=60;" ' Connection 3 Dim myConnection as New SqlClient.SqlConnection() myConnection.ConnectionString = "User ID=sa;" & _ "Password=password;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Connection TimeOut=30;" Visual Basic: Controlling Connection Pooling ' To disable connection pooling Dim cnNorthwind as New SqlClient.SqlConnection() cnNorthwind.ConnectionString = _ "Integrated Security=True;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Pooling=False;" cnNorthwind.ConnectionString = _ "Integrated Security=True;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Min Pool Size=10;" cnNorthwind.ConnectionString = _ "Integrated Security=True;" & _ "Initial Catalog=databasename;" & _ "Data Source=databaseserve;" & _ "Connection Lifetime=120;" Error handling for Database Errors Try Me.cnSQLNorthwind.ConnectionString = _ "Data Source=databaseserve;" & _ "Initial Catalog=Northwind;" & _ "Integrated Security=SSPI;" Me.cnSQLNorthwind.Open() Catch XcpSQL As System.Data.SqlClient.SqlException Dim sErrorMsg As String Dim se As System.Data.SqlClient.SqlError For Each se In XcpSQL.Errors Select Case se.Number Case 17 sErrorMsg = "Missing server" Case 4060 sErrorMsg = "Missing database" Case 18456 sErrorMsg = "Missing user name or password" Case Else sErrorMsg = se.Message End Select MessageBox.Show(sErrorMsg, "SQL Server Error: " & se.Number, MessageBoxButtons.OK MessageBoxIcon.Error) Next Catch XcpInvOp As System.InvalidOperationException MessageBox.Show("Close the connection first!", _ "Invalid Operation MessageBoxButtons.OK, MessageBoxIcon.Error) Catch Xcp As System.Exception ' generic exception handler MessageBox.Show(Xcp.Message, "Unexpected Exception MessageBoxButtons.OK, MessageBoxIcon.Error) End Try Visual Basic Code for Retrieving Output and Return Values in stored procedures CREATE PROCEDURE dbo.CountProductsInCategory ( @CatID int, @CatName nvarchar(15) OUTPUT ) AS DECLARE @ProdCount int SELECT @CatName = Categories.CategoryName, @ProdCount = COUNT(Products.ProductID) FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (Categories.CategoryID = @CatID) GROUP BY Categories.CategoryName RETURN @ProdCount ' Set input parameters, execute the stored procedure, then ' retrieve the output parameter and the return value ' ExecuteNonQuery is the most efficient cmd.Parameters("@CatID").Value = 1 cn.Open() cmd.ExecuteScalar() ' could use any ExecuteX method cn.Close() MessageBox.Show("Category name: " & _ cmd.Parameters("@CatName").Value & _ "Number of products in category: cmd.Parameters("@RETURN_VALUE").Value) Loading and xml document into a data set Private Const PurchaseSchema As String = "C:\sampledata\sample.xsd" Private myDS as DataSet Private Sub Load_XSD() Dim myStreamReader As StreamReader = Nothing Try myStreamReader = New StreamReader(PurchaseSchema) myDS = New DataSet() myDS.ReadXmlSchema(myStreamReader) Catch e as Exception msgbox("Exception: " & e.ToString()) Finally If Not myStreamReader Is Nothing Then myStreamReader.Close() End If End Try End Sub Writing XML Data to a File Private Sub SaveWriteXML() Try Dim StrPurchaseSchema as String myDS = New DataSet() myDS.ReadXml("C:\sampledata\PurchaseOrder.xmlnXmlReadMode.ReadSchema) myDS.WriteXml("C:\sampledata\CurrentOrders.xml, XmlWriteMode.IgnoreSchema) Catch e as Exception Console.WriteLine("Exception: " & e.ToString()) End Try End Sub Ritesh Kesharwani SQL Star Intlernational Ltd. [EMAIL PROTECTED] [EMAIL PROTECTED] www.Riteshk.blogspot.com Cell - 9849976150 ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
