|
0) I don't understands the need for such code (maybe it's just the way you presented your example), but...
1) You'd call that method something like this: RulesBL bl = this.GetBL<RulesBL>(connstring, dbName);
2) IMHO, it seems to me that you're genericizing the wrong end of things (again, it may be the example you presented). I've done something similar, but I genericized the DAL (the base class for my BLL objects) to accept any model class I specify. My DAL contains the generic model handling, and the BLL objects (I have over 20) are concrete and simply call the DAL. I use whatever appropriate BLL object is necessary in my controllers to get/set data, and the DAL handles the models using reflection.
In my controller I have this:
DBObjectMyDB bll = new DBObjectMyDB(connstring);
SqlParameter[] parameters = new SqlParameter[]{new SqlParameter("@id", idValue)};
List<MyObject> list = bll.GetMyObect(parameters);
or this:
DBObjectMyDB bll = new DBObjectMyDB(connstring);
int recs = bll.SaveMyObject(model);
In the BLL:
public List<MyObject> GetMyObject(SqlParameter[] parameters)
{
List<MyObject> list = this.ExecuteStoredProc<MyObject>("spGetMyObject", parameters);
return list;
}
or
public int SaveMyObject(MyObject model)
{
int recs = this.ExecuteStoredProc("spSaveMyObject", model.ToSqlParameters);
return recs;
}
The DAL is where all the generic stuff is.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
The class I use in the project is a SQLite database that helps static classes. It can be called directly without using instantiation.
Originally there are a lot of related classes on the Internet. After using a class, you can't call it.
private void TextBox2_TextChanged(object sender, EventArgs e)
{
SQLiteCommand cmdInsert = new SQLiteCommand(mConn);
string sql = "INSERT INTO KD(postID,ddtime)values(@postID,@ddtime)";
SQLiteParameter[] cmdParms = new SQLiteParameter[]{
new SQLiteParameter("@postID", (textBox2.Text)),
new SQLiteParameter("@ddtime", DateTime.Now)
};
cmd.ExecuteNonQuery(sql,cmdParms);
}
The SQLiteHelper class source is as follows
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLiteQueryBrowser
{
public static class SQLiteHelper
{
public static string connectionString = "Data Source=" + Application.StartupPath + "/KDDB.db";
#region 执行数据库操作(新增、更新或删除)，返回影响行数
public static int ExecuteNonQuery(SQLiteCommand cmd)
{
int result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
try
{
result = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
public static int ExecuteNonQuery(string commandText, CommandType commandType = CommandType.Text)
{
int result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0)
throw new ArgumentNullException("commandText");
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
try
{
result = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
public static int ExecuteNonQuery(string commandText, CommandType commandType = CommandType.Text, params SQLiteParameter[] cmdParms)
{
int result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0)
throw new ArgumentNullException("commandText");
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, commandType, commandText, cmdParms);
try
{
result = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
#endregion
#region 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
public static object ExecuteScalar(SQLiteCommand cmd)
{
object result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
try
{
result = cmd.ExecuteScalar();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
public static object ExecuteScalar(string commandText, CommandType commandType = CommandType.Text)
{
object result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0)
throw new ArgumentNullException("commandText");
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
try
{
result = cmd.ExecuteScalar();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
public static object ExecuteScalar(string commandText, CommandType commandType = CommandType.Text, params SQLiteParameter[] cmdParms)
{
object result = 0;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0)
throw new ArgumentNullException("commandText");
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, true, commandType, commandText, cmdParms);
try
{
result = cmd.ExecuteScalar();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
#endregion
#region 执行数据库查询，返回SqlDataReader对象
public static DbDataReader ExecuteReader(SQLiteCommand cmd)
{
DbDataReader reader = null;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
SQLiteConnection con = new SQLiteConnection(connectionString);
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);
try
{
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
return reader;
}
....................
|
|
|
|
|
I suggest you leave "God" out of this, and edit this post so that the issue you are having is clearly stated.
Rather than dump a bunch of code, describe errors or unexpected results. Show where in the code the errors occur.
Describe what you have done to isolate the errors or unexpected results.
«Where is the Life we have lost in living? Where is the wisdom we have lost in knowledge? Where is the knowledge we have lost in information?» T. S. Elliot
|
|
|
|
|
Before you read my complaints at the bottom, it is quite good code; rather readable, and already using parameterized queries. If this were in a project, I would not change it. I do have some suggestions of course.
Dhjjf wrote: It can be called directly without using instantiation.
That's mostly limiting the existing class. Being able to create multiple connections and commands gives an added flexibility.
private void TextBox2_TextChanged(object sender, EventArgs e)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
using (SQLiteCommand cmd = con.CreateCommand())
{
try
{
cmd.CommandText = "INSERT INTO KD(postID,ddtime)values(@postID,@ddtime)";
SQLiteParameter[] cmdParms = new SQLiteParameter[]{
new SQLiteParameter("@postID", (textBox2.Text)),
new SQLiteParameter("@ddtime", DateTime.Now)
};
int recordsAffected = cmd.ExecuteNonQuery(cmdParms);
Debug.Assert(recordsAffected != 1);
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
throw;
}
}
} Also, your version would be harder to use than the OO-version; the original I can wrap in a decorator-pattern, extending its functionality without changing the original classes.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Three things:
1) Your error description makes no sense: "After using a class, you can't call it." You don;t use any classes, you are just calling static methods of a static class. You need to explain in a lot more detail exactly what you are doing, and what happens as a result.
2) Don't store data files - including databases - in your app folder. It will work in development, but it can fail in production because the app is installed under the "Program Files" folder which is a target for virus activity and is thus often protected from changes. See here for better ideas: Where should I store my data?[^]
3) Please learn about string.IsNullOrWhitespace rather than doing "empty string" tests yourself.
But we probably can't help you that much - this requires you app running along with your database (and probably user input) to work out what the problem might be and we have no access to any of those.
So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it, then a quick Google for "Visual Studio debugger" should give you the info you need.
Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.
Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using ObjectExtensions;
namespace DataModels
{
public partial class DBObject
{
public bool FailOnMismatch { get; set; }
public int TimeoutSecs { get; set; }
public string ConnectionString { get; set; }
public DBObject(string connStr)
{
this.ConnectionString = connStr;
this.TimeoutSecs = 300;
this.FailOnMismatch = false;
}
protected DataTable GetData(string cmdText, SqlParameter[] parameters=null, CommandType cmdType = CommandType.StoredProcedure)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
DataTable data = null;
using (conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
using (reader = cmd.ExecuteReader())
{
data = new DataTable();
data.Load(reader);
}
}
}
return data;
}
protected int SetData(string cmdText, SqlParameter[] parameters, CommandType cmdType = CommandType.StoredProcedure)
{
int result = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
using (conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
{
SqlParameter rowsAffected = null;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
if (cmdType == CommandType.StoredProcedure)
{
rowsAffected = parameters.FirstOrDefault(x=>x.Direction == ParameterDirection.ReturnValue);
if (rowsAffected == null)
{
rowsAffected = cmd.Parameters.Add(new SqlParameter("@rowsAffected", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue } );
}
}
}
result = cmd.ExecuteNonQuery();
result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
}
}
return result;
}
protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
{
T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
return result;
}
protected List<T> MakeEntityFromDataTable<T>(DataTable data)
{
Type objType = typeof(T);
List<T> collection = new List<T>();
if (data != null && data.Rows.Count > 0)
{
int matched = 0;
foreach(DataRow row in data.Rows)
{
T item = (T)Activator.CreateInstance(objType);
PropertyInfo[] properties = objType.GetProperties();
foreach (PropertyInfo property in properties)
{
if (data.Columns.Contains(property.Name))
{
Type pType = property.PropertyType;
var defaultValue = pType.GetDefaultValue();
var value = row[property.Name];
value = ConvertFromDBValue(value, defaultValue );
property.SetValue(item, value);
matched++;
}
}
if (matched != data.Columns.Count && this.FailOnMismatch)
{
throw new Exception("Data retrieved does not match specified model.");
}
collection.Add(item);
}
}
return collection;
}
public List<T> ExecuteStoredProc<T>(string storedProc, params SqlParameter[] parameters)
{
DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
public int ExecuteStoredProc(string storedProc, params SqlParameter[] parameters)
{
int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);
return result;
}
public List<T> ExecuteQuery<T>(string storedProc, params SqlParameter[] parameters)
{
DataTable data = this.GetData(storedProc, parameters, CommandType.Text);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
public int ExecuteQuery(string storedProc, params SqlParameter[] parameters)
{
int result = this.SetData(storedProc, parameters, CommandType.Text);
return result;
}
}
}
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 30-May-19 11:14am.
|
|
|
|
|
#realJSOP wrote: This is the way I do it
Not programming against the IDb interfaces? You can have the DbProvider create the appropriate connection, use the CreateCommand-factorymethod to create a command for that specific connection. It'd work the same, but suddenly for every possible DB-provider.
Still, very nice code, and very happy that you return the amount of records affected. Checking how many records have been affected (versus expected) is a simple way of validating.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
We only use Sql Server, so there's no need to use the IDB stuff. I don't know if there's tangible reazliation of performance improvements, but hey, this is the way the boss wanted to do it. I made it generic so we could present a reasonable alternative to using EF (which nobody in our shop likes - at all). It's light, and as fast as you can expect ADO to be despite our use of reflection. Most of the BLL methods in the inheriting classes are no more than two lines of code, and even most of those can be reduced to one.
All of our models that save to the db have a property that returns a SqlParameter[] array, so all we have to do is pass in (to the BLL class) the object we want to save, and the method uses a ToSqlParameters property in the call to the base DAL class.
When in motion, it's a thing of beauty. 
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Sounds reasonable
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
As far as I can see, the only call to ConvertFromDBValue<T> will always use object as the type parameter. I don't think there's any benefit to making the method generic.
protected static object ConvertFromDBValue(object obj, object defaultValue)
{
object result = (obj == null || obj == DBNull.Value) ? defaultValue : obj;
return result;
}
I could be wrong, because you've used var for the defaultValue variable, and haven't included the GetDefaultValue extension method. But I can't see how that method could possibly be declared as returning anything other than object.
Also, if you're targeting .NET 4.0 or later, you can just use null as the default value. According to the remarks of the PropertyInfo.SetValue method:
If this PropertyInfo object is a value type and value is null, then the property will be set to the default value for that type.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I always do things the long way around.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Whoops, wrong forum
I wrote a Win Forms app in vb, and took the advice to not use the registry, so I created an appSettings.json file that I store in the appData folder. It works fine on my machine, and on 7 others, but the file gets corrupt on one machine, in which an extra } is placed at the end of the json file. Then I go to read the file and my code bombs during deserialize.
What have I done, well I tried to streamline and optimize the code as much as possible. At this point I have run out of things to search for.
Public Shared Function GetSettings() As RootObject
Dim oRootObject As RootObject = Nothing
Dim appData = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim appPath = appData + My.Resources.AppSettingsJsonPath
If Not (File.Exists(appPath)) Then
Create_AppSettings()
End If
Try
Using jsonStream = New FileStream(appPath, FileMode.Open, FileAccess.Read)
Dim fileLen = jsonStream.Length
Dim fileData = New Byte(fileLen) {}
Dim jsonLen = jsonStream.Read(fileData, 0, fileLen)
Dim fileText = Encoding.UTF8.GetString(fileData)
oRootObject = JsonConvert.DeserializeObject(Of RootObject)(fileText)
End Using
Catch ex As Exception
ErrorLogging.NLog_Exception(ex, "Read appSettings.json")
End Try
Return oRootObject
End Function
Public Shared Sub WriteSettings(ByVal rootObject As RootObject)
Dim appData = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim appPath = appData + My.Resources.AppSettingsJsonPath
If Not (IsFileInUse(appPath)) Then
If rootObject.ProductInfo IsNot Nothing Then
rootObject.ProductInfo.Name = Application.ProductName
rootObject.ProductInfo.Version = Application.ProductVersion
End If
If rootObject.Settings IsNot Nothing Then
rootObject.Settings.Version = Application.ProductVersion
End If
Try
Dim oJs = JsonConvert.SerializeObject(rootObject, Formatting.Indented)
Do Until Not IsFileInUse(appPath)
Application.DoEvents()
Loop
Dim bytes() = New UTF8Encoding(True).GetBytes(oJs)
Using jsonStream = File.OpenWrite(appPath)
jsonStream.Write(bytes, 0, bytes.Length)
End Using
Catch ex As Exception
ErrorLogging.NLog_Exception(ex, "Write appSettings.json")
End Try
End If
End Sub
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 29-May-19 19:06pm.
|
|
|
|
|
Change your call to File.OpenWrite to:
Using jsonStream = New FileStream(appPath, FileMode.Create, FileAccess.Write)
What your code is doing with File.OpenWrite is opening a file with FileMode.OpenOrCreate, which will NOT completely overwrite the content of the file. It will open an existing file AND KEEP THE CONTENT IN IT for write.
Your code is failing because the file you're writing used to be at least one character longer than what you're writing now. When you write the new content of the file, only the bytes up to the Length of the content you're writing are overwritten. The remaining original content of the file remains intact.
Changing the FileMode to Create opens the file, but truncates the content so everything in the existing file is removed. Read up on it here[^].
Then go read up on creating a FileStream[^].
|
|
|
|
|
Oh!
That makes total sense now.
I didn't see that coming, nor did the extra } give me a clue as well.
I can see now when I updated the screen rectangle in the model, the file got longer.
Thank you very much!
Appreciated!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Oh, and you posted VB.NET code in a C# forum. Watch what you click on next time.
|
|
|
|
|
I had just closed a typescript project and reopened this vb project and was still thinking c#.
It's almost 5 and time to go home for me.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
how to download unread mails in c#
|
|
|
|
|
DO NOT REPOST YOUR QUESTION
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
You're going to have to provide a lot more information than that. What type of email server is it that you want to pull the mail from?
This space for rent
|
|
|
|
|
daily based on (unread mails)different mails like... PDF,links,job related mails
|
|
|
|
|
And what have you tried so far, other than asking pretty much the same question on successive days?
Where are you stuck?
What help do you need?
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Thank you for answering a question I didn't ask. I asked what type of email server you were going to be pulling it from. How do you plan to connect to the server? Are you going to use POP3? SMTP? IMAP?
The approach you take will be different depending on which connection type you are using. For instance, POP3 isn't going to keep track of unread emails for you.
This space for rent
|
|
|
|
|
how to download unread mails in console applications.
|
|
|
|
|
What messages, download from where, webform or winform ... ?
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
