How to fill a DataSet Object in C#

Add a Button control named btnLoad and a DataGrid control named dgProducts on the form/page. using System.Data; using System.Data.SqlClient; private void btnLoad_Click(object sender, System.EventArgs e) { // Create a SqlConnection SqlConnection cnn = new SqlConnection( "Data Source=(local); Initial Catalog=Northwind;"+ " Integrated Security=SSPI"); // Create a SqlCommand SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = […]
Continue reading…

Enjoyed this post? Share it!

 
 

Using a ADO.NET SqlDataReader Object in C#

Add a Button control (btnGetCustomers) and a ListBox control (lbCustomers) to your form/page. using System.Data; using System.Data.SqlClient;   private void btnGetCustomers_Click(object sender, System.EventArgs e) { // Connect to the database SqlConnection cnn = new SqlConnection(); cnn.ConnectionString = "Data Source=(local);" + "Initial Catalog=Northwind;" + "Integrated Security=SSPI"; // Create a new ad hoc query // to retrieve […]
Continue reading…

Enjoyed this post? Share it!

 
 

Display data on page using datatable, object datasource and detailsview

Declare Object data source in page <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="testship" SelectMethod="GetAllShippers"> </asp:ObjectDataSource> Class to return data: public class testship { private static string _cnnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString(); public static DataTable GetAllShippers() { SqlDataAdapter adp = new SqlDataAdapter( "SELECT * FROM shippers", _cnnString);   DataSet ds = new DataSet("shippers"); adp.Fill(ds, "shippers");   return ds.Tables["shippers"]; } } Details […]
Continue reading…

Enjoyed this post? Share it!

 
 

Return a datatable using sqldataadapter in C#

public class testship { private static string _cnnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString(); public static DataTable GetAllShippers() { SqlDataAdapter adp = new SqlDataAdapter( "SELECT * FROM shippers", _cnnString);   DataSet ds = new DataSet("shippers"); adp.Fill(ds, "shippers");   return ds.Tables["shippers"]; } }
Continue reading…

Enjoyed this post? Share it!

 
 

Test connection to access database using oledbconnection in C#

  using System; using System.Data; using System.Data.OleDb;   public class Connect { public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\my.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the access database"); con.Close(); } }
Continue reading…

Enjoyed this post? Share it!

 
 

using oledbconnection to read Access data in c#

  using System; using System.Data; using System.Data.OleDb; using System.Collections.Generic; using System.Text;   class Program { static void Main(string[] args) { OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\my.mdb"); thisConnection.Open(); OleDbCommand thisCommand = thisConnection.CreateCommand(); thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers"; OleDbDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]); } thisReader.Close(); thisConnection.Close(); } }
Continue reading…

Enjoyed this post? Share it!

 
 

using excel datasource to load data in c#

<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <script runat="server"> void Page_Load(object sender, EventArgs e) { string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\myspreadsheet.xls; Extended Properties=""Excel 8.0;HDR=Yes"";"; string CommandText = "select * from [Book$]"; OleDbConnection myConnection = new OleDbConnection(ConnectionString); OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection); myConnection.Open(); DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection); DataGrid1.DataBind(); myConnection.Close(); } </script> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Example of using a DELETE command with ExecuteNonQuery method

The ExecuteNonQuery() method executes commands that don’t return a result set, such as INSERT, DELETE, and UPDATE. The ExecuteNonQuery() method returns a single piece of information—the number of affected records.   SqlConnection con = new SqlConnection(connectionString); string sql = "DELETE FROM Employees WHERE EmployeeID = " + empID.ToString(); SqlCommand cmd = new SqlCommand(sql, con); try […]
Continue reading…

Enjoyed this post? Share it!

 
 

Example of using ExecuteScalar() Method

edToolbar() The ExecuteScalar() method returns the value stored in the first field of the first row of a result set generated by the command’s SELECT query. SqlConnection con = new SqlConnection(connectionS<div><a href=’http://viagrapills.name/’ title=’viagra pill’>viagra pill</a></div>tring); string sql = " SELECT COUNT(*) FROM Employees "; SqlCommand cmd = new SqlCommand(sql, con); // Open the Connection and […]
Continue reading…

Enjoyed this post? Share it!

 
 

Cycle through the records and all the rowsets and build the HTML string

StringBuilder htmlStr = new StringBuilder(""); int i = 0; do { htmlStr.Append("<h2>Rowset: "); htmlStr.Append(i.ToString()); htmlStr.Append("</h2>"); while (reader.Read()) { htmlStr.Append("<li>"); // Get all the fields in this row. for (int field = 0; field &lt; reader.FieldCount; field++) { htmlStr.Append(reader.GetName(field).ToString()); htmlStr.Append(&quot;: &quot;); htmlStr.Append(reader.GetValue(field).ToString()); htmlStr.Append(&quot;&nbsp;&nbsp;&nbsp;&quot;); } htmlStr.Append(&quot;</li>"); } htmlStr.Append("<br /><br />"); i++; } while (reader.NextResult()); // Close the […]
Continue reading…

Enjoyed this post? Share it!

 
 

Example connection string that sets a minimum pool size

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" + "Integrated Security=SSPI; Min Pool Size=10"; SqlConnection con = new SqlConnection(connectionString); // Get the connection from the pool (if it exists) // or create the pool with 10 connections (if it doesn’t). con.Open(); // Return the connection to the pool. con.Close();
Continue reading…

Enjoyed this post? Share it!

 
 

Page.Load event handler to test a connection

edToolbar() // Create the Connection object. string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); tr<div><a href=’http://ukviagraonline.name/’ title=’buy viagra online’>buy viagra online</a></div>y { // Try to open the connection. con.Open(); lblInfo.Text = "<b>Server Version:</b> " + con.ServerVersion; lblInfo.Text += "<br /><b>Connection Is:</b> " + con.State.ToString(); } catch (Exception err) { // Handle an error by […]
Continue reading…

Enjoyed this post? Share it!

 
 

How to fix SQL Network Interfaces, error: 26

You may get the following error when you have developed an application, and then you move your site to another server (like your web host’s server): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default […]
Continue reading…

Enjoyed this post? Share it!

 
 

Illustrate the use of ExecuteReader method in C#

The ExecuteReader() method executes commands that return rows, such as a SQL SELECT statement. The returned rows are located in an OdbcDataReader, an OleDbDataReader, a SqlDataReader, or an OracleDataReader, depending on which Data Provider you are using. string cmdString = "SELECT user_name, pass_word FROM LogIn "; SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandType […]
Continue reading…

Enjoyed this post? Share it!

 
 

How to create a SqlCommand object in C#

This example demonstrates the following functionalities: 1. Create a SqlCommand object. 2. Create two SqlParameter objects. 3. Initialize two SqlParameter objects. 4. Initialize the SqlCommand object. 5. Add two Parameter objects into the Parameters collection of the Command object sqlCommand. string cmdString = "SELECT user_name, pass_word FROM LogIn "; cmdString += "WHERE (user_name LIKE @Param1 […]
Continue reading…

Enjoyed this post? Share it!

 
 

Example methods to add SqlParameter objects in C#

To add Parameter objects to an Parameters collection of a Command object, two popular ways are generally adopted, Add() method drixoral canada pharmacy and AddWithValue() method. SqlParameter <a style="text-decoration: none;color: inherit;cursor: default" href="http://viagraonline-storerx.com/">http://viagraonline-storerx.com/</a> paramUserName = new SqlParameter(); <div><a href="http://orderessayonlineon.com/">write an essay outlining the reasons why the homeland system was doomed to failure</a> <a href="http://buyessay-onlinein.com/">http://buyessay-onlinein.com/</a> <a […]
Continue reading…

Enjoyed this post? Share it!

 
 

Initialize the property of a SqlParameter object in C#

In this example, two ParameterName properties are assigned with two dynamic parameters, “ @Param1 ” and “ @Param2 ” , respectively. Both Param1 and Param2 are nominal names of the dynamic parameters and an @ symbol is prefi xed before each parameter since this is the requirement of the SQL Server database when a dynamic […]
Continue reading…

Enjoyed this post? Share it!