How to get records from SQL to a label, textbox, or a datatable?

Make sure you import:

Imports System.Data
Imports System.Data.SqlClient

For C#:

using System.Data.SqlClient;
using System.Data;
using System.Configuration;
to get one record FROM PROCEDURE
see code
Public Function gettotal(ByVal VARIABLE As Int32) As Decimal
        Dim Total As Decimal
        Dim congetprodnum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("YOURWEBCONFIGCONNECTION").ConnectionString)
        Dim cmdproductnumber As New System.Data.SqlClient.SqlCommand("STOREPROCEDURE '" & vARIABLE & "'", congetprodnum)
        congetprodnum.Open()
        Total = cmdproductnumber.ExecuteScalar
        congetprodnum.Close()
        gettotal = Total
    End Function
TO GET ONE ROW FROM PROCEDURE
see code
Imports System.Data.SqlClient
Imports System.Data

 Protected Sub getcustomer()
        Dim dr As SqlDataReader
        Dim congetprodnum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("WEBCONFIGCONNECTIONSTRING").ConnectionString)
        congetprodnum.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = congetprodnum
        cmd.CommandText = "PROCEDURENAME"
        'MyDBFunc.DeriveSPParams(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        Dim pc As SqlParameterCollection = cmd.Parameters
        pc.Add("@PARAMETER", SqlDbType.Int, 4)
        cmd.Parameters("@PARAMETER").Value = Session("client.customerId")
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.Read = True Then
            Label1.Text = dr("FIELD1") & ""
            Label2.Text = dr("FIELD2") & ""
            Label3.Text = dr("FIELD3") & ""
            Label4.Text = dr("FIELD4") & ""
            Label5.TEXT = dr("FIELD5") & ""
            Label6.Text = dr("FIELD6") & ""
        End If
    End Sub
TO GET ONE ROW USING A SELECT STATEMENT
see code
Protected Sub verification()

        Dim dr As SqlDataReader
        Dim congetprodnum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("WEBCONFIGDATABASECONECTIONSTRINGNAME").ConnectionString)
        congetprodnum.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = congetprodnum
        cmd.CommandText = "select top 1 FIRSTNAME,LASTNAME from CUSTOMERS order by newid()"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.Read = True Then
            Session("FIRSTNAME") = dr("FIRSTNAME") & ""
            Session("LASTNAME") = dr("LASTNAME") & ""

        End If

    End Sub

To get a table based on any command (function)
see code
  Private Function databaseinvoke(ByVal command As String) As DataTable
        'Add Try statements
        Dim cns As New SqlConnection("Data Source=SQLSERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=USERID;Password=USERPASSWORD")
        Dim cn As New SqlConnection()
        Dim CustomersDataSet As New DataSet()
        Dim da As SqlDataAdapter
        Dim cmdBuilder As SqlCommandBuilder
        ' Dim congetprodnum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
        Dim congetprodnum As New System.Data.SqlClient.SqlConnection(cns.ConnectionString)

        congetprodnum.Open()
        da = New SqlDataAdapter(command, congetprodnum)
        cmdBuilder = New SqlCommandBuilder(da)
        da.Fill(CustomersDataSet)
        Return CustomersDataSet.Tables(0)
    End Function

C# Data Connection

   
static bool SaveDocument(byte[] document)
        {

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@reportdata",document);
            command.CommandText = "Insert Into [PDFCollection](StatementData)Values(@reportdata)";
            databaseinvoke(command);
            return true;
        }
        static DataTable databaseinvoke(SqlCommand command)
        {

            string SQLConnectionString =System.Configuration.ConfigurationManager.ConnectionStrings["webconfigorappconfigname"].ConnectionString;
            SqlConnection cns = new SqlConnection(SQLConnectionString);
            DataSet CustomersDataSet = new DataSet();
            SqlDataAdapter da = default(SqlDataAdapter);
            command.Connection = cns;
            cns.Open();
            da = new SqlDataAdapter(command);
            da.Fill(CustomersDataSet);
            cns.Close();

            if (CustomersDataSet.Tables.Count > 0)
            {
                return CustomersDataSet.Tables[0];
            }
            DataTable dt = new DataTable();
            return dt;
        }
  static bool retrievepdf()
        {
            //Select Statementdata from [CustomItemStatement] where ID=4
            string FileName=@"C:\mypdf.pdf";
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            //command.Parameters.AddWithValue("@reportdata", document);
            command.CommandText = "Select Statementdata from [PDFCollection] where ID=5";
            DataTable dt= databaseinvoke(command);
            byte[] fileBinary = (byte[])dt.Rows[0][0];

            FileStream fileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            fileStream.Write(fileBinary, 0, fileBinary.Length);
            fileStream.Flush();
            fileStream.Close();
            return true;

        }

Related Posts

Comments are closed.


Warning: Use of undefined constant XML - assumed 'XML' (this will throw an Error in a future version of PHP) in /homepages/35/d428352912/htdocs/WilliamMendoza/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1048