Posts Tagged Class
Class to Simplify and Minimize Code for Stored Procedures and TSQL
Posted by Kelly's Chronicles in Uncategorized on April 17, 2016
First of all if you are using TSQL in your code you really need to get in a another line of work. It isn’t scalable, difficult to maintain and is generally a bad practice. But I know some of you still do it so I did include it. This class will allow you to in a few lines of code do your select, update, insert or delete statements in a very few lines of code. All that is required of the developer is to list the values in order (if you are using stored procedures) that are required. Also included is how to declare a new instance of a sqlparametercollection – which you aren’t suppose to be able to do. I ended up not needing it but included it just in case someone needs to do that someday. Also if you are using a strongly typed dataset there is a method included of how to do that. After the code is example of usage. Have a great day!
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Data.SqlClient;
public class clsData
{
public SqlConnection sqlconn = new SqlConnection();
public string Error_Message;
public List<SqlParameter> ParamList = new List<SqlParameter>();
public ArrayList ParamValues = new ArrayList();
public int SQLExecuteNonQueryValue;
public clsData()
public enum SQLAction
{
SelectAction,
UpdateInsertDeleteAction
}
public virtual DataTable DataAction(clsData.SQLAction Action, DataTable dt, string cmdtext, string ParamValues = “”)
{
SQLExecuteNonQueryValue = -1;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdtext;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlconn;
this.GetParameterList(cmdtext);
if (!string.IsNullOrEmpty(Error_Message)) {
return null;
}
if (!string.IsNullOrEmpty(ParamValues)) {
this.ParseSQLParameterValues(ParamValues);
for (i = 0; i <= this.ParamList.Count – 1; i++) {
cmd.Parameters.AddWithValue(this.ParamList[i].ToString(), this.ParamValues[i].ToString());
}
}
//Debug.WriteLine(cmd.CommandText)
try {
cmd.Connection.Open();
switch (Action) {
case SQLAction.SelectAction:
dt.Load(cmd.ExecuteReader());
break;
case SQLAction.UpdateInsertDeleteAction:
//Check this value to make sure everything went ok
SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return null;
}
cmd.Connection.Close();
} catch (Exception ex) {
Error_Message = “There was a problem with ” + cmdtext + “. “;
return null;
}
//removing duplicate table from dataset
for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
if (ds_Copy.Tables(i).TableName == dt.TableName) {
ds_Copy.Tables(i).Clear();
ds_Copy.Tables(i).Columns.Clear();
}
}
//placing datatable in the dataset
ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
Error_Message = “”;
return dt;
}
public DataTable DataActionTSQL(clsData.SQLAction Action, DataTable dt, string sqlstring)
{
SQLExecuteNonQueryValue = -1;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sqlstring;
cmd.Connection = sqlconn;
try {
cmd.Connection.Open();
switch (Action) {
case SQLAction.SelectAction:
dt.Load(cmd.ExecuteReader());
break;
case SQLAction.UpdateInsertDeleteAction:
//Check this value to make sure everything went ok
SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return null;
}
cmd.Connection.Close();
} catch (Exception ex) {
Error_Message = “There was a problem with ” + sqlstring + “. “;
return null;
}
//removing duplicate table from dataset
for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
if (ds_Copy.Tables(i).TableName == dt.TableName) {
ds_Copy.Tables(i).Clear();
ds_Copy.Tables(i).Columns.Clear();
}
}
//placing datatable in the dataset
ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
Error_Message = “”;
return dt;
}
private List<SqlParameter> GetParameterList(string ProcName)
{
//Get the parameters for the selected stored procedure
List<SqlParameter> inputParamList = new List<SqlParameter>();
ParamList.Clear();
using (SqlConnection cn = new SqlConnection()) {
DispatcherTool.My.MySettings Settings = new DispatcherTool.My.MySettings();
cn.ConnectionString = Settings.SRAConnectionString;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = cn;
myCommand.CommandText = ProcName;
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
try {
cn.Open();
SqlCommandBuilder.DeriveParameters(myCommand);
cn.Close();
} catch (Exception ex) {
Error_Message = “There was a problem with the connection to the database.”;
return null;
}
//Dim sqlparams As SqlParameterCollection = DirectCast(GetType(SqlParameterCollection).GetConstructor(BindingFlags.NonPublic Or BindingFlags.Instance, Nothing, Type.EmptyTypes, Nothing).Invoke(Nothing), SqlParameterCollection)
foreach (SqlParameter param in myCommand.Parameters) {
if (param.Direction == System.Data.ParameterDirection.Input || param.Direction == System.Data.ParameterDirection.InputOutput) {
//sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType.ToString())
//Debug.WriteLine(param.ParameterName & ” – ” & param.SqlDbType.ToString())
inputParamList.Add(param);
//Else
// sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType)
//Debug.WriteLine(param.ParameterName & ” -2 ” & param.SqlDbType)
}
}
}
this.ParamList = inputParamList;
Error_Message = “”;
return this.ParamList;
}
public void ParseSQLParameterValues(string ValueString)
{
ParamValues.Clear();
string[] parts = ValueString.Split(new char[] { ‘,’ });
string part = null;
foreach (string part_loopVariable in parts) {
part = part_loopVariable;
ParamValues.Add(part);
}
}
//’Public Shared Function ConvertToTypedDataTable(Of T As {Data.DataTable, New})(ByVal dtBase As Data.DataTable) As T
//’ Dim dtTyped As New T
//’ dtTyped.Merge(dtBase)
//’ Return dtTyped
//’End Function
}
And then it’s use…..
private void Button1_Click(System.Object sender, System.EventArgs e)
{
Strongly_Typed_DataSet.usp_Your_Stored_Procedure dt = new Strongly_Typed_DataSet.usp_Your_Stored_Procedure();
DataGridView1.DataSource = cn.DataAction(clsData.SQLAction.SelectAction, dt, ds.usp_Get_PrinterName_for_PickList_Printing.ToString, “Tubing”);
//DataGridView1.DataSource = cn.DataActionTSQL(clsData.SQLAction.SelectAction, dt, “SELECT *FROM Your_Table”)
}
action, AddWithValue, Also, ArrayList, Check, Class, Clear, Close, clsData, cmdtext, CODE, Collections, columns, CommandText, CommandType, Configuration, Connection, ConvertToTypedDataTable, Count, Data, DataAction, DataActionTSQL, Database, DataSet, DataTable, Debug, DeriveParameters, developer, Diagnostics, DirectCast, Direction, DispatcherTool, ds_Copy, dtBase, Else, EmptyTypes, Error_Message, example, Exception, ExecuteNonQuery, ExecuteReader, foreach, FUNCTION, Generic, GetConstructor BindingFlags, GetParameterList, GetType, Handle, Input, InputOutput, inputParamList, instance, List, Load, Merge, method, MissingSchemaAction, myCommand, MySettings, NonPublic, Open, Param, ParameterDirection, ParameterName, parameters, ParamList, ParamValues, ParseSQLParameterValues, part_loopVariable, procedure, Procedures, ProcName, Public, Return, SelectAction, Settings, Split, SQLAction, SqlClient, SqlCommand, SqlCommandBuilder, sqlconn, SqlConnection, SqlDbType, SQLExecuteNonQueryValue, SqlParameter, SqlParameterCollection, sqlparams, statements, StoredProcedure, System, TableName, Tables, TSQL, Type, UpdateInsertDeleteAction, usage, WriteLine
Class Set as DataContext in XAML without Code Behind
Posted by Kelly's Chronicles in .NET, C#, XAML on May 6, 2012
In dealing with a problem at work and I am probably as guilty as anyone of relying on code behind to do basic functions without soley using XAML. Why? Because that’s the way I have always done it. But it’s a new day and time to learn new ways to do things. So here we go….
This example shows a class set as datacontext – the code behind file is completely empty.
Have a great week….
<Window x:Class=”cSharpTest.MainWindow”
xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
xmlns:vm=”clr-namespace:cSharpTest”
Title=”MainWindow” Height=”350″ Width=”525″>
<Window.Resources>
<vm:MyData x:Key=”ViewModel”/>
</Window.Resources>
<Grid DataContext=”{StaticResource ViewModel}”>
<ListBox Name=”MyListBox” ItemsSource=”{Binding Primes}”/>
</Grid>
</Window>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace cSharpTest
{
class MyData
{
public MyData()
{
_primes = new int[5] { 1, 3, 5, 7, 11 };
}
private int[] _primes;
public int[] Primes
{
get { return this._primes; }
}
}
}
BEHIND, c#, Class, CODE, Collections, csharp, DataContext, example, Generic, Grid, ItemsSource, Linq, ListBox, Microsoft, Name, namespace, Presentation, resources, schemas, StaticResource, System, Text, Title, ViewModel, Width, XAML, xmlns, _primes
Recent Comments
Archives
- April 2018
- January 2018
- December 2017
- June 2017
- May 2017
- May 2016
- April 2016
- September 2015
- July 2014
- June 2014
- November 2012
- October 2012
- June 2012
- May 2012
- April 2012
- January 2012
- September 2011
- August 2011
- July 2011
- May 2009
- April 2009
- March 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- August 2008
- July 2008
- June 2008
- May 2008
Get or Write Image Metadata with vb.net
Posted by Kelly's Chronicles in .NET, vb.net on September 1, 2011
I recently started a new contract with a local company and their project is quite image intensive. One of the tasks set before me was to store custom data in an image. Things such as the title, comments and keywords. Turned out to be quite an extensive undertaking. With the help of some other sources, I came up with this code. Ironically as it turned out we can’t use my solution because of some issues with custom image formats that are not respected in Microsoft Windows. But it is here for you! I will post the C# version soon. Also pay attention to your encoding. This was an issue that tripped me up here for a while.
Imports System.Collections.Generic
Imports System.Text
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Reflection
Imports System.IO
Public Class clsReadMetaData
Public Function ReadEXIFMetadata(ByVal filepath As String) As ImageMetadata
Dim fs As New FileStream(filepath, FileMode.Open, FileAccess.Read)
Dim image__1 As Image = Image.FromStream(fs)
Dim imagePropertyItems As PropertyItem() = image__1.PropertyItems
Dim imageMetadata As New ImageMetadata()
For Each pi As PropertyItem In imagePropertyItems
Select Case CType(pi.Id, EXIFProperty)
Case EXIFProperty.Title
imageMetadata.Title = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Title = Encoding.UTF32.GetString(pi.Value)
Exit Select
Case EXIFProperty.Author
imageMetadata.Author = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Author = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case EXIFProperty.Keywords
imageMetadata.Keywords = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Keywords = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case EXIFProperty.Comments
imageMetadata.Comments = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Comments = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case Else
Exit Select
End Select
Next
fs.Close()
Return imageMetadata
End Function
Public Sub SaveEXIFMetadata(ByVal image As Image, ByVal metadata As ImageMetadata, ByVal filepath As String)
SaveEXIFMetadataProperty(image, EXIFProperty.Title, metadata.Title, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Author, metadata.Author, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Keywords, metadata.Keywords, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Comments, metadata.Comments, filepath)
End Sub
Private Sub SaveEXIFMetadataProperty(ByVal image As Image, ByVal [property] As EXIFProperty, ByVal propertyValue As String, ByVal filepath As String)
Dim propertyItem As PropertyItem = CreatePropertyItem()
propertyItem.Id = CInt([property])
‘ Type=1 means Array of Bytes.
propertyItem.Type = 2
propertyItem.Len = propertyValue.Length
‘propertyItem.Value = Encoding.Unicode.GetBytes(propertyValue)
propertyItem.Value = Encoding.UTF8.GetBytes(propertyValue)
image.SetPropertyItem(propertyItem)
image.Save(filepath)
End Sub
Private Function CreatePropertyItem() As PropertyItem
Dim ci As System.Reflection.ConstructorInfo = GetType(PropertyItem).GetConstructor(BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.[Public], Nothing, New Type() {}, Nothing)
Return DirectCast(ci.Invoke(Nothing), PropertyItem)
End Function
End Class
Public Enum EXIFProperty
Title = 40091
Author = 40093
Keywords = 40094
Comments = 40092
End Enum
Public Class ImageMetadata
Private _title As String = String.Empty
Private _author As String = String.Empty
Private _keywords As String = String.Empty
Private _comments As String = String.Empty
Public Sub New()
Me._title = String.Empty
Me._author = String.Empty
Me._keywords = String.Empty
Me._comments = String.Empty
End Sub
Public Sub New(ByVal title As String, ByVal author As String, ByVal keywords As String, ByVal comments As String)
Me._title = title
Me._author = author
Me._keywords = keywords
Me._comments = comments
End Sub
Public Property Title() As String
Get
Return Me._title
End Get
Set(ByVal value As String)
Me._title = value
End Set
End Property
Public Property Author() As String
Get
Return Me._author
End Get
Set(ByVal value As String)
Me._author = value
End Set
End Property
Public Property Keywords() As String
Get
Return Me._keywords
End Get
Set(ByVal value As String)
Me._keywords = value
End Set
End Property
Public Property Comments() As String
Get
Return Me._comments
End Get
Set(ByVal value As String)
Me._comments = value
End Set
End Property
End Class
.NET Framework, Array, author, BindingFlags, Bytes, Case, Class, Close, Collections, Comments, ConstructorInfo, CreatePropertyItem, custom, Data, DirectCast, Enum, FileMode, filepath, FileStream, FromStream, FUNCTION, Generic, GetBytes, GetConstructor, GetType, Image, ImageMetadata, imagePropertyItems, Imports, instance, Keywords, Length, Metadata, Microsoft, NonPublic, Open, Private, PropertyItem, PropertyItems, propertyValue, Public, Read, ReadEXIFMetadata, Reflection, Return, Save, Select, SetPropertyItem, solution, System, tasks, Text, Title, Type, Unicode, Value, vb.net, version, Windows, Write, _author, _comments, _keywords, _title
8 Comments