Hello I have a question about creating an insert query. Is there a way I can do Convert.DBNull with an int and a datetime? Also I would like to know the most efficient way I should be doing CRUD with C# WPF. I am currently using a listview to view my data from my DB I use a SqlCommand and a SqlDataReader to put my data into a list<myobject> and I use a foreach to add the list to the listview. I am wondering if I should be using a Datatable instead and possibly a DataGrid?
I am wondering if I could use a Create method like this?
public static void CreateProduct(Product product)
{
using (SqlConnection connection = ConnectionString.GetSqlConnection())
{
connection.Open();
string sqlCreate = "INSERT INTO Products (ProductName, ProductPrice, ProductQuantity, " +
"ProductTax, ProductTotal, ProductListedDate, ProductPurchasedDate, UserID, ReceiptID " +
"VALUES(@pn, @pp, @pq, @pta, @pto, @pld, @ppd, @uid, @rid)";
using (SqlCommand cmdCreate = new SqlCommand(sqlCreate, connection))
{
cmdCreate.Parameters.AddWithValue("@pn", product.ProductName);
cmdCreate.Parameters.AddWithValue("@pp", product.ProductPrice);
cmdCreate.Parameters.AddWithValue("@pq", product.ProductQuantity);
cmdCreate.Parameters.AddWithValue("@pta", product.ProductTax);
cmdCreate.Parameters.AddWithValue("@pto", product.ProductTotal);
cmdCreate.Parameters.AddWithValue("@pld", product.ProductListedDate);
cmdCreate.Parameters.AddWithValue("@ppd", product.ProductPurchasedDate);
cmdCreate.Parameters.AddWithValue("@uid", product.UserID);
cmdCreate.Parameters.AddWithValue("@rid", product.ReceiptID);
cmdCreate.ExecuteNonQuery();
}
}
}
public static int CreateNewUser(User newUser)
{
string SQLinsertQuery = "INSERT INTO Users (Username, Password, IsAdmin, UserCreatedDate) " +
"VALUES(@username, @password, @isadmin, @usercreateddate)";
SqlCommand cmdCreate = new SqlCommand(SQLinsertQuery, connection);
cmdCreate.Parameters.AddWithValue("@username", newUser.Username);
cmdCreate.Parameters.AddWithValue("@password", newUser.Password);
cmdCreate.Parameters.AddWithValue("@isadmin", newUser.IsAdmin);
cmdCreate.Parameters.AddWithValue("@usercreateddate", newUser.UserCreatedDate);
try
{
connection.Open();
cmdCreate.ExecuteNonQuery();
string SQLselect = "SELECT @@IDENTITY FROM Users";
SqlCommand cmdSelect = new SqlCommand(SQLselect, connection);
int userId = Convert.ToInt32(cmdSelect.ExecuteScalar());
return userId;
}
catch (SqlException sql)
{
sql.Message.ToString();
throw sql;
}
catch (Exception ex)
{
ex.Message.ToString();
return -1;
}
finally { connection.Close(); }
}
What I have tried:
using listview and other create method