Direct method from SQL command text to DataSet
public DataSet GetDataSet(string ConnectionString, string SQL)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = SQL;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
///conn.Open();
da.Fill(ds);
///conn.Close();
return ds;
}
Obtaining a dataset from a SQL Server database
You have to use provider for MSSQL - SqlDataAdapter class.
string CnStr=@"put_here_connection_string";
SqlDataAdapter adp=new SqlDataAdapter("select * from tableName",CnStr);
DataSet ds=new DataSet();
adp.Fill(ds,"TableName");
Get the generated SQL statement from a SqlCommand object?
Whilst not perfect, here's something I knocked up for TSQL - could be easily tweaked for other flavors... If nothing else it will give you a start point for your own improvements :)
This does an OK job on data types and output parameters etc similar to using "execute stored procedure" in SSMS. We mostly used SPs so the "text" command doesn't account for parameters etc
public static String ParameterValueForSQL(this SqlParameter sp)
{
String retval = "";
switch (sp.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
break;
case SqlDbType.Bit:
retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";
break;
default:
retval = sp.Value.ToString().Replace("'", "''");
break;
}
return retval;
}
public static String CommandAsSql(this SqlCommand sc)
{
StringBuilder sql = new StringBuilder();
Boolean FirstParam = true;
sql.AppendLine("use " + sc.Connection.Database + ";");
switch (sc.CommandType)
{
case CommandType.StoredProcedure:
sql.AppendLine("declare @return_value int;");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= ");
sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";");
}
}
sql.AppendLine("exec [" + sc.CommandText + "]");
foreach (SqlParameter sp in sc.Parameters)
{
if (sp.Direction != ParameterDirection.ReturnValue)
{
sql.Append((FirstParam) ? "\t" : "\t, ");
if (FirstParam) FirstParam = false;
if (sp.Direction == ParameterDirection.Input)
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL());
else
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output");
}
}
sql.AppendLine(";");
sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");");
}
}
break;
case CommandType.Text:
sql.AppendLine(sc.CommandText);
break;
}
return sql.ToString();
}
this generates output along these lines...
use dbMyDatabase;
declare @return_value int;
declare @OutTotalRows BigInt = null;
exec [spMyStoredProc]
@InEmployeeID = 1000686
, @InPageSize = 20
, @InPage = 1
, @OutTotalRows = @OutTotalRows output
;
select 'Return Value' = convert(varchar, @return_value);
select '@OutTotalRows' = convert(varchar, @OutTotalRows);
Why does my SQL dataset always return a count of 0?
Well, for those of you that might seem interested, I was not naming my table. This prevented my program from reading the information from the correct place. Consequently, null was all I ever got. I knew it had to be something basic. Thank you for your interest.
Read SQL Table into C# DataTable
Here, give this a shot (this is just a pseudocode)
using System;
using System.Data;
using System.Data.SqlClient;
public class PullDataTest
{
// your data table
private DataTable dataTable = new DataTable();
public PullDataTest()
{
}
// your method to pull data from database to datatable
public void PullData()
{
string connString = @"your connection string here";
string query = "select * from table";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
conn.Close();
da.Dispose();
}
}
Difference of Direct Query and stored procedure in vb.net
The direct SQL is good because it's more portable. Hopping from SQL Server to another database? Change the text in the program, you don't need database access. Also good if your organization doesn't let you create stored procedures. (All db access in one person's hands, all desktop/web code in another's.)
The stored procedure is good because it's compiled. For a simple query, it may not matter much, but for longer, multi-statement queries it probably will. If there's an error in the query, you change it in one place on the server, there's no rolling out a new executable to a dozen desktops.
The answer is, test performance both ways, if there's not a significant difference then you should choose based on what makes maintenance the least hassle.
I prefer procedures, but your situation may vary.
Related Topics
Selenium: Unable to Access Iframe and Data Inside It
Set System Time Zone from .Net
C# - How to Detect a Windows Shutdown/Logoff and Cancel That Action (After Asking the User)
"Do Not Use Abstract Base Class in Design; But in Modeling/Analysis"
How to Get the Name of Color While Having Its Rgb Value in C#
Accuracy of Textrenderer.Measuretext Results
Why am I Getting Sehexception When Calling Roleenvironment.Getconfigurationsettingvalue("Mykey")
Why Is Webbrowser_Documentcompleted() Firing Twice
Should Idisposable.Dispose() Be Made Safe to Call Multiple Times
Programmatically Getting the Last Filled Excel Row Using C#
Object' Does Not Contain a Definition for 'X'
C# - Check If File Is Text Based
Convert String to Brushes/Brush Color Name in C#
Extracting Text from PDFs in C#
Moq + Unit Testing - System.Reflection.Targetparametercountexception: Parameter Count Mismatch