How to setup MariaDB, SQLite and MySQL on C#.Net
I found the following guide useful for setup databases for projects (.Net). This includes the setup guides for MySQL, MariaDB and SQLite connections , can be used with MVC and Repository pattern and Dapper ORM
C#.Net SQL connection string
This is a C#.Net SQL App.config connection string snippet
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="Dolphin_Accounts.Properties.Settings.sql_connection"
connectionString="Data Source=.\sqlexpress;Initial Catalog=dolphine;User ID=sa;Password=" />
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2"/>
</startup>
</configuration>
MS SQL case .. when snippet
This MS SQL case when snippet
SELECT invoice ,
case
when isnull(sum(tr.cramount),0)>isnull(sum(tr.dramount),0) then isnull(sum(tr.cramount),0)-isnull(sum(tr.dramount),0)
else 0
end as cr,
case
when isnull(sum(tr.dramount),0)>isnull(sum(tr.cramount),0) then isnull(sum(tr.dramount),0)-isnull(sum(tr.cramount),0)
else 0
end as dr
FROM transactions AS tr INNER JOIN accounts AS ac ON tr.DRID
= ac.id inner join accounts ac1 on tr.crid=ac1.id group by invoice,tr.drid
How to convert a column type in SQL Query/ the convert function in SQL
Suppose you have query in which want to compare two column from different table, each with number and string type (MS SQL Server). The task is here is , convert the number column value into a string .
We can using SQL standard function Convert to cast the type of the column into another SQL type , the basic syntax as follows
convert(<type>,<column-name>)
select e.name,e.code from emp e depart d where e.code=convert(varchar,d.ecode) order by e.name
In the above example both table have employee code but in different type. The only possible way to match the where clause is have identical type , using the SQL Convert
Following SQL post may help you learn more
How to convert a column type in Access Query
Suppose you have query in which want to compare two column from different table, each with number and string type. The task is here is , convert the number column value into a string .
We can using access functions such Clng, Cstr to convert the column into type as follows
select name, cstr(ecode) from emp
These functions are helpful when multiple table are to be join based on some conditions , each with different datatype.
Following SQL post may help you learn more
Copy Table Schema and Data From One Database to Another Database in SQL Server
In SQL Server you can easily copy values as well as schema/structure from one table to another using following commands
select * into db1.dbo.<destination table> from db2.dbo.<source table>The limitation of this method is that, only copies the table schema , data. and leave the objects, indexes, triggers or constraints behind.
For example in the above command will copy columns to the destination table and leave identity column as regular, your new table will not hold any identity columns .
Solution to this problem is generate the scripts using Script Wizard and then execute the script to generate all objects and indexes
All of the table structure,index,triggers,constraints will be created for your new database.
Now you are ready to copy data from one database from another using following insert command
insert into <destination table> (col1,col2 ...)
select col1,col2... from fin_app_data.dbo.groups
Please leave identity column behind.
sp_hel is the alternative command to oracle SQL’s desc command, which will display the table attributes like, columns type, constraints etc
sp_help <table name>
As we know , can facilitate Autocomplete feature to Text box, DatagridView controls in C#.Net. We can also fetch auto text from data tables too, by using dataview object.
GroupTableAdapter = new SqlDataAdapter("select name from AccountGroups", con);
GroupTableDataset = new DataSet();
GroupTableAdapter.Fill(GroupTableDataset, "AccountGroups");
GroupTableView = new DataView(GroupTableDataset.Tables[0]);
AutoCompleteStringCollection autotext = new AutoCompleteStringCollection();
foreach (DataRowView row in Common.GroupTableView)
{
autotext.Add(row["name"].ToString());
}
txt_gname.AutoCompleteMode = AutoCompleteMode.Suggest; txt_gname.AutoCompleteSource = AutoCompleteSource.CustomSource; txt_gname.AutoCompleteCustomSource = autotext;
drop all three section of code in load or initialize event and it will work.
Database programming is easy with Visual Studio.Net projects, it offers a variety of methods to visualize the programming logic. Here in our example, we demonstrate how you can extract specific rows from a TableView, which is the local representation of the SQL Table/Database Table View.
First I need a table view, like
ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
ProductTableAdapter.Fill(ProductTableDataset, "ProductMaster");
ProductTableView = new DataView(ProductTableDataset.Tables[0]);
Now I am going to find some rows for a specific product in the table view using the Find() method.
taekproduct="Citrizine Tab"; Common.ProductTableView.Sort="product"; int i = Common.ProductTableView.Find(takeproduct);
First I need to specify the Sort column which is the same column with the value I want to search. Second the product itself.
The Find() returns the row number instead of the row itself which can be used to access the row you want. If it’s 1 you can make sure you succeeded to find the row with takeproduct value otherwise the value will be -1.
Then I extract the tax rate from the table.
if (i != -1)
{ takecgst = takesgst = takeigst = 0;
double.TryParse( Common.ProductTableView[i]["cgst"].ToString(),out cgstrate);
double.TryParse(Common.ProductTableView[i]["sgst"].ToString(), out sgstrate);
double.TryParse(Common.ProductTableView[i]["igst"].ToString(), out igstrate);}
that’s all I got today.
When we have the problem with READ ONLY SQL DATABASE(It can happen while we trying to attach different version database file), first up all we need to make the security of ldf and mdf file to everyone-Full Control.( if not working, do the same for folder and drive in which the database file resides. )
And then simply execute the script
USE [master]
Go
Alter database [readonlydb] set READ_WRITE WITH NO_WAIT
GO
Or use the query without the NO_WAIT option.
USE [master]
Go
Alter database [readonlydb] set READ_WRITE
GO
Developer stories 