How to access data of tables inside DataConnection UWP - c#

So I connected the database (also tested the connection) work well
So how can I get the data out of it ???
Inside the App.cs I ve created a connectionstring
public string connectionString = "Server = DESKTOP-Q42NBTE; Database = FirstDataBase; Trusted_Connection = true";
public string ConnectionString
{
get => connectionString; set => connectionString = value;
}
Also created a class to hold all the datas
Class BooksData:
public class BooksData
{
public string Title { get; set; }
public string Description { get; set; }
public string ImageFile { get; set; }
public int Price { get; set; }
public int PublisherID { get; set; }
public int AuthorID { get; set; }
public int BookID { get; set; }
}
And a method for communication
public ObservableCollection<BooksData> GetBooks(string connectionString)
{
const string GetBooksQuery = "select * from Books";
var books = new ObservableCollection<BooksData>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetBooksQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var book = new BooksData();
book.Title = reader.GetString(0);
book.Price = reader.GetInt32(1);
book.ImageFile = reader.GetString(2);
book.PublisherID = reader.GetInt32(3);
book.AuthorID = reader.GetInt32(4);
book.BookID = reader.GetInt32(5);
}
}
}
}
}
return books;
}
catch (Exception eSql)
{
Debug.WriteLine("Exception: " + eSql.Message);
}
return null;
}
But nothing is working
Here is my Database Info

Related

Retrieving Images form database ASP.net

I've been working on a project using the adventure works database. The Website it's self is almost complete except for one issue and that's how to handle the varbinary images in the SQL database. I have a Data Access Class that returns the products from the database and a product.cs Class to get/set the values. I've seen tutorials on reading the data but, I would like to have to images in product.cs class but no idea how to properly do it. I've never worked with retrieving Images from a MSSQL database so my knowledge on that is less then perfect.
Here the productDB access class
[DataObject]
public class ProductDB
{
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<Product> GetProducts()
{
List<Product> productList = new List<Product>();
SqlConnection con = new SqlConnection(GetConnectionString());
string sel = "Select statement goes here"
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Product product;
while (rdr.Read())
{
product = new Product();
product.ProductID = rdr["ProductID"].ToString();
product.Name = rdr["Name"].ToString();
product.ProductNumber = rdr["ProductNumber"].ToString();
product.ListPrice = Convert.ToInt32(rdr["ListPrice"]);
product.ProductDescriptionID = Convert.ToInt32(rdr["ProductDescriptionID"]);
product.ProductModelID = Convert.ToInt32(rdr["ProductModelID"]);
product.Description = rdr["Description"].ToString();
productList.Add(product);
}
rdr.Close();
return productList;
}
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings[
"AdventureWorks"].ConnectionString;
}
}
and the product class
public class Product
{
public Product()
{
}
public string ProductID { get; set; }
public string Name { get; set; }
public string ProductNumber { get; set; }
public int ListPrice { get; set; }
public int Weight { get; set; }
public int Size { get; set; }
public int ProductDescriptionID { get; set; }
public int ProductModelID { get; set; }
public string Description { get; set; }
}

Empty result when calling SQL Server stored procedure

I am building a WebAPI using ASP.NET to expose some stored procedures I have implemented in a database. I am using ADO.NET to call the stored procedures.
I have a controller:
public class AuthController : ApiController
{
[HttpGet]
[ActionName("AuthSearch")]
public AuthorizationResult GetAuthSearch(Guid? id, [FromUri]AuthorizationQuery Query)
{
SqlConnection con = new SqlConnection("Connection String");
SqlDataReader reader = null;
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_AUTH_SEARCH", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("#GUID", id);
cmd.Parameters.AddWithValue("#AUTHNO", Query.AuthNo);
cmd.Parameters.AddWithValue("#STATUS", Query.Status);
cmd.Parameters.AddWithValue("#REQDATE_BEGIN", Query.ReqDateBegin);
cmd.Parameters.AddWithValue("#REQDATE_END", Query.ReqDateEnd);
cmd.Parameters.AddWithValue("#REQDATE_OP", Query.ReqDateOp);
cmd.Parameters.AddWithValue("#AUTDATE_BEGIN", Query.AuthDateBegin);
cmd.Parameters.AddWithValue("#AUTDATE_END", Query.AuthDateEnd);
cmd.Parameters.AddWithValue("#AUTDATE_OP", Query.AuthDateOp);
cmd.Parameters.AddWithValue("#EXPDATE_BEGIN", Query.ExpDateBegin);
cmd.Parameters.AddWithValue("#EXPDATE_END", Query.ExpDateEnd);
cmd.Parameters.AddWithValue("#EXPDATE_OP", Query.ExpDateOp);
cmd.Parameters.AddWithValue("#REFPROV", Query.RefProv);
cmd.Parameters.AddWithValue("#REQPROV_FIRST", Query.ReqProvFirst);
cmd.Parameters.AddWithValue("#REQPROV_LAST", Query.ReqProvLast);
cmd.Parameters.AddWithValue("#MEMB_FIRST", Query.MembFirst);
cmd.Parameters.AddWithValue("#MEMB_LAST", Query.MembLast);
cmd.Parameters.AddWithValue("#MEMB_ID", Query.MembId);
cmd.Parameters.AddWithValue("#SORT_COLUMN", 1);
reader = cmd.ExecuteReader();
AuthSearchResult auth = null;
if (reader.Read())
{
auth = new AuthSearchResult();
auth.AuthNo = reader[0].ToString();
}
return auth;
}
catch (Exception e)
{
AuthorizationResult auth = null;
auth = new AuthorizationResult();
auth.Descr = e.ToString();
return auth;
}
finally
{
con.Close();
}
}
}
And some model classes:
public class AuthorizationResult
{
public string AuthNo { get; set; }
public string Descr { get; set; }
public string MembId { get; set; }
public string MembName { get; set; }
public string MembSex { get; set; }
public string MembDob { get; set; }
public string MembHp { get; set; }
public string ProvName { get; set; }
public string AuthDate { get; set; }
public string ReqDate { get; set; }
public AuthorizationResult()
{
AuthNo = " ";
Descr = " ";
MembId = " ";
MembName = " ";
MembSex = " ";
MembDob = " ";
MembHp = " ";
ProvName = " ";
AuthDate = " ";
ReqDate = " ";
}
}
public class AuthorizationQuery
{
public string AuthNo { get; set; }
public string Status { get; set; }
public string ReqDateBegin { get; set; }
public string ReqDateEnd { get; set; }
public string ReqDateOp { get; set; }
public string AuthDateBegin { get; set; }
public string AuthDateEnd { get; set; }
public string AuthDateOp { get; set; }
public string ExpDateBegin { get; set; }
public string ExpDateEnd { get; set; }
public string ExpDateOp { get; set; }
public string RefProv { get; set; }
public string ReqProvFirst { get; set; }
public string ReqProvLast { get; set; }
public string MembFirst { get; set; }
public string MembLast { get; set; }
public string MembId { get; set; }
public AuthorizationQuery()
{
AuthNo = "";
Status = "";
ReqDateBegin = "";
ReqDateEnd = "";
ReqDateOp = "";
AuthDateBegin = "";
AuthDateEnd = "";
AuthDateOp = "";
ExpDateBegin = "";
ExpDateEnd = "";
ExpDateOp = "";
RefProv = "";
ReqProvFirst = "";
ReqProvLast = "";
MembFirst = "";
MembLast = "";
MembId = "";
}
}
However when I run my project and navigate to
localhost/api/Auth/AuthSearch?id=GUID
I get an empty result set. If I run the same stored procedure in SQL Server using the same GUID value, I get a non empty result set.
I've used this same SP in a VB.NET web forms app without any issues.
I've ensured that the connection string is correct and I've double checked that the GUID I'm using is correct. What else am I missing?
When I run the same SP on SQL Server
Perhaps...
I'd of course change my strategy is to avoid using " ".
(The 'else' condition in place if you are forced to supply all parameters.)
if (!String.IsNullOrEmpty(Query.AuthNo))
cmd.Parameters.AddWithValue("#AUTHNO", Query.AuthNo);
else
cmd.Parameters.AddWithValue("#AUTHNO", DBNull.Value);
By the way, strongly recommend you implement USING() since it takes care of DISPOSE and CLOSE, etc.
using (var ds = new DataSet())
{
using (var con = new SqlConnection("Connection String"))
{
using (var cmd = new SqlCommand("SP_AUTH_SEARCH", con))
{
using (var adapter = new SqlDataAdapter(cmd))
{
con.Open();
adapter.Fill(ds);
}
}
}
AuthorizationResult auth = null;
auth = new AuthorizationResult();
foreach (DataRow dr in ds.Tables[0].Rows)
{
auth.AuthNo = dr[0].ToString();
}
return auth;
}
you can try this
cmd.Parameters.Add("#GUID", SqlDbType.UniqueIdentifier).Value = id;
if your #GUID is of type UniqueIdentifier if its a varchar or nvarchar then try this
cmd.Parameters.AddWithValue("#GUID", id.ToString().ToUpper());
to convert the type GUID to String
Edit
i noticed in your sp the guid is upper case and in code guid converts string to lower case maybe this was causing problem so try adding this .ToUpper() to make the string uppercase

c# Derived Class issue

At the bottom of this message is a class and controller for that class. I have to do this for a couple hundred SQL tables. Yeah.
What I'd like to do is to be able to use a more generic base controller such that a specific controller would be something like:
public class IMS_ProductController : IMS_BaseController
public IEnumerable<IMS_Table> _recordset {get; set;}
string _tablename = "IMS_Product";
string _keyname = "ProductID";
}
and my base contoller would be like the IMS_ProductController below.
The issue is the first line above. How to I take care of _recordset and more specifically, <IMS_Table> which will be different for each of the SQL tables. You can see in the controller below (which, again, I'd like to turn into a generic controller) that I do things like:
var table = new List<IMS_Table>();
or
IMS_Table t = new IMS_Table();
Things like that.
Any suggestions would be incredibly helpful.
Thank you!
Chris
namespace IMS.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Linq;
//using System.Runtime.Serialization.Formatters.Binary;
[AttributeUsage(AttributeTargets.Property, Inherited = true)]
[Serializable]
public class MappingAttribute : Attribute
{
public string ColumnName = null;
}
public class IMS_Product
{
[Mapping(ColumnName = "ProductID")]
[Key]
public Guid ProductId { get; set; }
[Mapping(ColumnName = "Name")]
[Required]
public string Name { get; set; }
[Mapping(ColumnName = "Description")]
public string Description { get; set; }
[Mapping(ColumnName = "PortalID")]
public int PortalID { get; set; }
[Mapping(ColumnName = "Smith_ProductID")]
public int Smith_ProductID { get; set; }
[Mapping(ColumnName = "IsDigital")]
public int IsDigital { get; set; }
[Mapping(ColumnName = "PublisherID")]
public long PublisherID { get; set; }
[Mapping(ColumnName = "Released")]
public DateTime Released { get; set; }
[Mapping(ColumnName = "Length")]
public long Length { get; set; }
[Mapping(ColumnName = "CreatedOn")]
public DateTime CreatedOn { get; set; }
[Mapping(ColumnName = "CreatedBy")]
public int CreatedBy { get; set; }
[Mapping(ColumnName = "ModifiedOn")]
public DateTime ModifiedOn { get; set; }
[Mapping(ColumnName = "ModifiedBy")]
public int ModifiedBy { get; set; }
[Mapping(ColumnName = "Url")]
[StringLength(283)]
public string Url { get; set; }
[Mapping(ColumnName = "dnnFileID")]
public int dnnFileID { get; set; }
[Mapping(ColumnName = "dnnFolderID")]
public int dnnFolderID { get; set; }
[Mapping(ColumnName = "TypeTagID")]
public Guid TypeTagID { get; set; }
}
public partial class IMS_Table : IMS_Product { }
public partial class IMS_ProductController
{
public IEnumerable<IMS_Table> _recordset {get; set;}
string _tablename = "IMS_Product";
string _keyname = "ProductID";
T MapToClass<T>(SqlDataReader reader) where T : class
{
T returnedObject = Activator.CreateInstance<T>();
List<PropertyInfo> modelProperties = returnedObject.GetType().GetProperties().OrderBy(p => p.MetadataToken).ToList();
for (int i = 0; i < modelProperties.Count; i++)
try
{
modelProperties[i].SetValue(returnedObject, Convert.ChangeType(reader.GetValue(i), modelProperties[i].PropertyType), null);
}
catch { }
return returnedObject;
}
public void gets(string keyval)
{
string sql = string.Format("SELECT * from {0} where {1}='{2}'", _tablename, _keyname, keyval);
getIt(sql);
}
public string gets(string keyval, string where)
{
string sql = string.Format("SELECT * from {0} where {1}='{2}' {3}", _tablename, _keyname, keyval, where);
try
{
getIt(sql);
return "Sucess: " + sql;
}
catch
{
return "Error: " + sql;
}
}
public void sets(string keyval,string field, string value)
{
setIt(keyval, field, value);
}
private void getIt(string strSQL)
{
var table = new List<IMS_Table>();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(strSQL, con))
{
IMS_Table t = new IMS_Table();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
t = MapToClass<IMS_Table>(reader);
table.Add(t);
}
_recordset = table;
reader.Close();
reader.Dispose();
}
cmd.Dispose();
}
con.Close();
con.Dispose();
}
}
private void setIt(string keyval, string field, string value)
{
var products = new List<IMS_Table>();
var strSQL = string.Format("update {0} set {1} = '{2}' where {3}='{4}'", _tablename, field, value, _keyname, keyval);
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand(strSQL, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
}
}
}
Check #Henk Holterman's comments
Change your class to public partial class IMS_ProductController<T> where T :new()
Change MapToClass to T MapToClass(IDataRecord record). Note that DataReader implements IDataRecord.
Replace IMS_Table with T everywhere
Final Answer:
namespace IMS.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Linq;
[AttributeUsage(AttributeTargets.Property, Inherited = true)]
[Serializable]
public class MappingAttribute : Attribute
{
public string ColumnName = null;
}
public partial class IMS_Controller<IMS_WorkingTable> where IMS_WorkingTable : new()
{
private readonly string tablename;
private readonly string keyname;
protected IMS_Controller(string tablename, string keyname)
{
this.tablename = tablename;
this.keyname = keyname;
}
public string _tablename { get { return tablename; } }
public string _keyname { get { return keyname; } }
public IEnumerable<IMS_WorkingTable> _recordset;
IMS_WorkingTable MapToClass(IDataRecord record)
{
IMS_WorkingTable returnedObject = Activator.CreateInstance<IMS_WorkingTable>();
List<PropertyInfo> modelProperties = returnedObject.GetType().GetProperties().OrderBy(p => p.MetadataToken).ToList();
for (int i = 0; i < modelProperties.Count; i++)
try
{
modelProperties[i].SetValue(returnedObject, Convert.ChangeType(record.GetValue(i), modelProperties[i].PropertyType), null);
}
catch { }
return returnedObject;
}
public void gets(string keyval)
{
string sql = string.Format("SELECT * from {0} where {1}='{2}'", _tablename, _keyname, keyval);
getIt(sql);
}
public string gets(string keyval, string where)
{
string sql = string.Format("SELECT * from {0} where {1}='{2}' {3}", _tablename, _keyname, keyval, where);
try
{
getIt(sql);
return "Sucess: " + sql;
}
catch
{
return "Error: " + sql;
}
}
public void sets(string keyval, string field, string value)
{
setIt(keyval, field, value);
}
private void getIt(string strSQL)
{
var table = new List<IMS_WorkingTable>();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(strSQL, con))
{
IMS_WorkingTable t = new IMS_WorkingTable();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
t = MapToClass((IDataRecord)reader);
table.Add(t);
}
_recordset = table;
reader.Close();
reader.Dispose();
}
cmd.Dispose();
}
con.Close();
con.Dispose();
}
}
private void setIt(string keyval, string field, string value)
{
var products = new List<IMS_WorkingTable>();
var strSQL = string.Format("update {0} set {1} = '{2}' where {3}='{4}'", _tablename, field, value, _keyname, keyval);
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand(strSQL, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
}
}
}
namespace IMS.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
public partial class IMM_StoreOrder
{
[Mapping(ColumnName = "StoreOrderID")]
[Key]
public Guid StoreOrderID { get; set; }
[Mapping(ColumnName = "StoreUserId")]
public Guid StoreUserId { get; set; }
[Mapping(ColumnName = "OrderID")]
[Required]
[StringLength(128)]
public string OrderID { get; set; }
[Mapping(ColumnName = "OrderDate")]
public DateTime OrderDate { get; set; }
[Mapping(ColumnName = "Status")]
[StringLength(256)]
public string Status { get; set; }
[Mapping(ColumnName = "DeletedFlag")]
public bool DeletedFlag { get; set; }
[Mapping(ColumnName = "IPAddress")]
[StringLength(50)]
public string IPAddress { get; set; }
}
public partial class IMS_StoreOrderController : IMS_Controller<IMM_StoreOrder>
{
public IMS_StoreOrderController() : base("IMM_StoreOrder", "StoreOrderID") { }
}
}

C# Class Library for Data Layer - DataTable conversion

I am using a class library for my "data layer" and I am trying to create a method to load results from a SP into a datagrid via the .net DataTable.
I am having trouble returning the datatable (dt) object because of error "cannot implicitly convert type datatable to my custom type.
Here are some code snippets:
public DesignationTable GetDesignationTable(string designationName)
{
using (SqlConnection conn = DB.GetSqlConnection())
{
DesignationTable dt = new DataTable();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = #"GET_DESG_DETAILS_BY_ATTRIBUTE";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("DESG_ATTRIBUTE", System.Data.SqlDbType.Text);
p1.Value = designationName;
cmd.Parameters.Add(p1);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
}
return dt;
}
}
public class DesignationTable
{
public string DESG_ATTRIBUTE { get; set; }
public string DESG_ATTRIBUTE_DESC { get; set; }
public string DESG { get; set; }
public string DESG_NAME { get; set; }
public string TYPE { get; set; }
public string TYPE_DESC { get; set; }
public string DESG_PROJECT { get; set; }
public string DESG_PROJECT_DESC { get; set; }
public DateTime START_DATE { get; set; }
public DateTime END_DATE { get; set; }
public string STATUS { get; set; }
public void Load(SqlDataReader dr)
{
DESG_ATTRIBUTE = dr["DESG_ATTRIBUTE"].ToString();
DESG_ATTRIBUTE_DESC = dr["DESG_ATTRIBUTE_DESC"].ToString();
DESG = dr["DESG"].ToString();
DESG_NAME = dr["DESG_NAME"].ToString();
TYPE = dr["TYPE"].ToString();
TYPE_DESC = dr["TYPE_DESC"].ToString();
DESG_PROJECT = dr["DESG_PROJECT"].ToString();
DESG_PROJECT_DESC = dr["DESG_PROJECT__DESC"].ToString();
//START_DATE = DateTime.Parse(reader["START_DATE"].ToString());
//START_DATE = DateTime.Parse(reader["END_DATE"].ToString());
STATUS = dr["STATUS"].ToString();
}
}
Is this line correct?
DesignationTable dt = new DataTable();
Should it not be
DataTable dt=new DataTable();
then .Load() method can be used.

items not adding to a dictionary

i want to add items in a sql database to a dictionary but the values enter as null in the object reference heres the code im using atm
public static Dictionary<string, prizedbinfo> dbprizes = new Dictionary<string, prizedbinfo>();
private void LoadData(string dataloc)
{
if (!File.Exists(dataloc))
{
MessageBox.Show(dataloc + " not found.");
return;
}
var connection = new SQLiteConnection("Data Source=" + dataloc);
connection.Open();
var datacommand = new SQLiteCommand("SELECT prizeID, createdOn, expiresOn, modifiedOn, status, redeemedOn, giftedOn, claimedOn FROM mySnackData", connection);
List<string[]> datas = ExecuteStringCommand(datacommand, 9);
foreach (string[] row in datas)
{
if (!Program.dbprizes.ContainsKey(row[0]))
{
Program.dbprizes.Add(row[0], new prizedbinfo(row));
}
}
connection.Close();
}
and the prizedbinfo object is
class prizedbinfo
{
public prizedbinfo(string[] dbdata)
{
string prizeID = dbdata[0];
string createdOn = dbdata[1];
string expiresOn = dbdata[2];
string modifiedOn = dbdata[3];
string status = dbdata[4];
string redeemedOn = dbdata[5];
string giftedOn = dbdata[6];
string claimedOn = dbdata[7];
string name = dbdata[8];
}
public string prizeID { get; set; }
public string createdOn { get; set; }
public string expiresOn { get; set; }
public string modifiedOn { get; set; }
public string status { get; set; }
public string redeemedOn { get; set; }
public string giftedOn { get; set; }
public string claimedOn { get; set; }
public string name { get; set; }
}
i have tested it with breakpoints and all the data is correctly added to datas and row but not to the prizedbinfo object for some reason
Remove the string keyword from all your variables in the prizedbinfo constructor. You're creating local variables that immediately go out of scope, so you're losing your values.
public prizedbinfo(string[] dbdata)
{
prizeID = dbdata[0];
createdOn = dbdata[1];
expiresOn = dbdata[2];
modifiedOn = dbdata[3];
status = dbdata[4];
redeemedOn = dbdata[5];
giftedOn = dbdata[6];
claimedOn = dbdata[7];
name = dbdata[8];
}

Resources