How to get records from SQL to a label, textbox, or a datatable?
Make sure you import: For C#:
Imports System.Data
Imports System.Data.SqlClient
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
to get one record FROM PROCEDURE
see codePublic 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 codeImports 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 codeProtected 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;
}